Pass array literal to PostgreSQL function

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:

CREATE OR REPLACE FUNCTION get_questions(vcode text[])  RETURNS TABLE(id int, title text, code text)  LANGUAGE sql AS$func$SELECT q.id, q.title, q.codeFROM   questions qWHERE  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 FUNCTION get_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.codeFROM   questions qLEFT   JOIN unnest($1) c(code) USING (code)WHERE  c.code IS NULL;

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