Postgresql – How to pass an array to a plpgsql function with VARIADIC parameter

arrayfunctionsparameterplpgsqlpostgresql

I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.

I have already defined the function

CREATE OR REPLACE FUNCTION mix_table_fields(input_table_name VARCHAR, output_table_name VARCHAR, VARIADIC field_names VARCHAR[])
[...]

Now, when I try to call it:

SELECT mix_table_fields('articles'::VARCHAR, 'output_random_articles'::VARCHAR, array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]) ;

I get the following error : " the function mix_table_fields(character varying, character varying, character varying[]) does not exist".

I have had this error before. It cannot recognize the signature of the function (its input parameters).

When I use pgAdmin3 to look at the function definition, it is:

CREATE OR REPLACE FUNCTION public.mix_table_fields(
    IN input_table_name character varying,
    IN output_table_name character varying,
    VARIADIC field_names character varying[])

I cannot see what's wrong.

And if there is a way of getting rid of the ::VARCHAR at each parameter when calling the function, it would be easier.

Best Answer

To pass an actual array for a VARIADIC parameter, you must use the keyword VARIADIC in the call:

SELECT mix_table_fields('art'::VARCHAR
                      , 'out'::VARCHAR
                      , VARIADIC array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]);

Even works without explicit type casts in your case. Function type resolution can find the best candidate for untyped string literals without ambiguity - as long as the function is not overloaded:

SELECT mix_table_fields('art', 'out', VARIADIC array['type','colour','size','price']);

Or with an array literal:

SELECT mix_table_fields('art', 'out', VARIADIC '{type,colour,size,price}');

Without the keyword VARIADIC, Postgres expects a list of element values like:

SELECT mix_table_fields('art', 'out', 'type', 'colour', 'size','price');

... which is the whole point of a VARIADIC function parameter: to be able to call it with a variable number of parameters of the element type, so you don't need the array wrapper. (Also the reason why a VARIADIC parameter must come last in the parameter list: else the call could be ambiguous.) If you don't need this feature, use a plain varchar[] parameter without VARIADIC in the function definition to begin with.

Postgresql – In PostgreSQL, is there a type-safe first() aggregate function

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Postgresql – Passing array from one plpgsql function to another

You can avoid various complications by passing values as values with the USING clause:

CREATE OR REPLACE FUNCTION foo(linktable regclass, 
                               inttable  regclass, 
                               verttable regclass)
RETURNS void AS
$func$
BEGIN
   EXECUTE format(
      'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t'
     , linktable)
   USING  $1, $2, $3;  -- you could use parameter names as well
END
$func$ LANGUAGE plpgsql;

And pass the generated array directly in a single SELECT.

Note that $1, $2, $3 in EXECUTE reference the USING clause and only happen to be in sync with ordinal numbers of function parameters in USING $1, $2, $3. Same notation, independent scope!

After fixing the immediate cause for the error (like @Craig commented):

WHERE   int.id ANY %L

WHERE   int.id = ANY (%L)

Simplify the 2nd function in a similar fashion:

CREATE OR REPLACE FUNCTION bar(linktable regclass,
                               inttable  regclass,
                               verttable regclass,
                               intersection_ids int[])

    -- ... SNIP

    EXECUTE format('
        INSERT INTO %1$s
               (int_id, ref_link_id, match_link_id, ref_azimuth, match_azimuth)
        SELECT  int.id, l1.id      , l2.id
              , degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom)))
                degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
        FROM    %2$s int
        JOIN    %3$s v1 ON v1.intersection_id = int.id
        JOIN    %3$s v2 ON v2.intersection_id = int.id
        JOIN    %4$s l1 ON l1.target_node = v1.node_id
        JOIN    %4$s l2 ON l2.source_node = v2.node_id
                     AND l2.road_id <> l1.road_id
        WHERE   int.id = ANY ($1)    -- fix syntax and pass as value!
        AND     l1.road_id IS NOT NULL
        AND     l2.road_id IS NOT NULL
        ', temptable,
           inttable,
           verttable,
           linktable)
    USING intersection_ids;

    ...

The whole setup might be simplified further.

Best Answer

Related Solutions

Postgresql – In PostgreSQL, is there a type-safe first() aggregate function

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Postgresql – Passing array from one plpgsql function to another

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`