Pass Array Literal To Postgresql Function

I have a Postgres function which contains a select statement. I need to add a condition using a passed in variable containing an array of string values. CREATE OR REPLACE FUNCTION

Solution 1:

SQL NOT IN works with sets. Since you are passing an array, use <> ALL.

You have to be careful not to involve any NULL values with such an expression, because NULL <> anything never evaluates to TRUE and therefore never qualifies in a WHERE clause.

Your function could look like this:

CREATEOR REPLACE FUNCTION get_questions(vcode text[])
  RETURNSTABLE(id int, title text, code text)
  LANGUAGEsqlAS
$func$
SELECT q.id, q.title, q.code
FROM   questions q
WHERE  q.code <>ALL ($1);
$func$;

Call:

SELECT*FROM get_questions('{qcode2, qcode2}');

Or (alternative syntax with an array constructor):

SELECT*FROM get_questions(ARRAY['qcode2', 'qcode2']);

Or you could use a VARIADIC parameter:

CREATE OR REPLACE FUNCTIONget_questions(VARIADIC vcode text[]) ...

... and pass a list of values:

SELECT*FROM get_questions('qcode2', 'qcode2');

Details:

• Return rows matching elements of input array in plpgsql function

Major points:

Using a simple SQL function since there is nothing in your question that would require the procedural elements of PL/pgSQL.

The input parameter is an array of text: text[]

To return multiple rows from your query use RETURNS TABLE for the return type.

Referring to the in parameter with the positional parameter $1 since referring by name was only introduced with version 9.2 for SQL functions (as opposed to plpgsql functions where this has been around for some versions now).

Table-qualify column names that would otherwise conflict with OUT parameters of the same name defined in the RETURNS clause.

LEFT JOIN unnest($1) / IS NULL

Faster for long arrays (> ~ 80 elements, it depends):

SELECT q.id, q.title, q.code
FROM   questions q
LEFTJOINunnest($1) c(code) USING (code)
WHERE  c.code ISNULL;

This variant (as opposed to the above) ignores NULL values in the input array.