PostgreSQL: function with array argument-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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