PostgreSQL: function with array argument

Question

For the first time I'm trying to create function in PostgreSQL:

This function must accept parameter with array type. Array contains sequence of biginteger values. Size of array not arbitrary, but known in advance.

 create function get_total (cols ARRAY) returns biginteger AS
   $BODY$
       begin 
         // Some logics 
       end;
   $BODY$

Usage in query

 select 
     stats.value1, 
     stats.value2,
     get_total(array_agg(ARRAY[stats.value2, stats.value1])) 
 from stats;

It returns error:

 type cols[] does not exist
 SQL state: 42704

When I run in Select only array_agg(ARRAY[stats.value2, stats.value1]), I see, that array created successfully. So the problem in function parameter.

What am I doing wrong?

Jim Jones · Accepted Answer · 2021-11-03 14:02:14Z

3

You have to declare the parameter as bigint[], which reads an array of type bigint e.g.

CREATE OR REPLACE FUNCTION get_total (bigint[]) 
RETURNS bigint AS
$$
BEGIN
 -- your fancy logic goes here
END;
$$ LANGUAGE plpgsql

Function call:

SELECT get_total(ARRAY[1111111111111,1111111111111]);

An elegant alternative is to declare the parameter as VARIADIC. Doing so you may call your function with multiple parameters,e.g.:

CREATE OR REPLACE FUNCTION get_total (VARIADIC bigint[]) 
RETURNS bigint AS
$$
BEGIN  
 -- your fancy logic goes here
END;
$$ LANGUAGE plpgsql;

Function call:

SELECT get_total(1111111111111,1111111111111,1111111111111);

Demo: db<>fiddle

edited Nov 3, 2021 at 14:02

answered Nov 3, 2021 at 13:16

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jelly Over a year ago

When I specify only type of parameter, how could I use it hereinafter (without name of parameter)? For example I was able to use it in LOOP like so: FOR col IN cols

Jim Jones Over a year ago

@Andrew you can name the parameter if you want, or use $1 to access it, where 1 means the first parameter. In my db<>fiddle I used it within a query. Can you see it?

Jim Jones Over a year ago

@Andrew I believe this the third column of this fiddle might clarify things a bit: dbfiddle.uk/…

Jelly Over a year ago

Thank you, now always is clear

Laurenz Albe · Accepted Answer · 2021-11-03 13:40:44Z

1

The syntax cols ARRAY is the same as cols[] and means “an array with elements of type cols”. That's why you get that error message for the function definition.

If the element type is bigint, the function should be defined as

CREATE FUNCTION get_total(cols bigint ARRAY) RETURNS bigint

answered Nov 3, 2021 at 13:40

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: function with array argument

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related