Pass array literal to PostgreSQL function

Question

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 get_questions(vcode text)
  RETURN return_value as $f$
DECLARE vresult return_value;

BEGIN
--snip--

SELECT id, title, code
FROM questions WHERE code NOT IN (vcode);

--snip--

questions table:

id ,title, code
1, "title1", "qcode1"
2, "title2", "qcode2"
3, "title3", "qcode3"
4, "title4", "qcode4"

How should the vcode literal be formatted in PHP and what should be the syntax of the condition?

Using PostgreSQL 9.1.1, PHP 5.3.6, pg_query_params.

Answer 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:

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.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 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.code
FROM   questions q
LEFT   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.