CODEWITHSUNDEEP
sqlarrayspostgresqldynamic-sqlexecute
MichaelCold
MichaelCold

Reputation: 13

Using array as a parameter in postgres dynamic sql

I am trying to run a dynamic query like this (just an idea): 

    declare 
    params text[] ;
    begin 
      params := ARRAY['30', 'sometext'];
      return execute QUERY 'select id, label from "content" where id>$1 and label = $2' using params;
    end ;

The thing is, that my params array can have variable length - I can prepare an appropriate select query, but I would like to execute it using "using" clause, with the array containing all parameters marked with $.

Would be something like that possible or should I build the entire select statement with values already put in a statement (however, this is what I would like to avoid).

Upvotes: 1

Views: 3048

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324295

Even if expanding an array into a parameter-list for USING was possible (which it isn't), you'd run into trouble the first time you wanted to mix data types.

Have you looked into format(..) with the %L specifier? It won't solve the mixed-types issues, but other than that it'll work.

regress=> SELECT format('SELECT a FROM b WHERE z = %L and x = %L and y = %L', VARIADIC ARRAY[1, 2, 3]::integer[]);
                        format                         
-------------------------------------------------------
 SELECT a FROM b WHERE z = '1' and x = '2' and y = '3'
(1 row)

While the values are quoted, that's fine, it's legal to single-quote integers in SQL.

Upvotes: 1

Daniel Vérité
Daniel Vérité

Reputation: 61506

An array is itself a possible value for a parameter, so it cannot be also a container for parameter values.

In your example, the pl/pgsql interpreter would take the entire array after USING as the value for the $1 parameter.

Besides, if the language supported automatic array unnesting into $N parameters, it would generally not fit well because values inside an array are constrained to the same type, whereas $N parameters are not.

Upvotes: 1

Related Questions