21

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
DevR
  • 295
  • 1
  • 3
  • 8

1 Answers1

36

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 with array literal:

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

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

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the response. Setting the IN column type to "text[]" was what I hadn't tried. I thought I could just pass in an array of text values, but apparently not. – DevR Jun 17 '13 at 03:10
  • @user2254435: If it's easier for you to pass a comma-separated string as `text`, you can do that, and transform it to an array inside the function with [`string_to_array()`](http://www.postgresql.org/docs/current/interactive/functions-array.html#ARRAY-FUNCTIONS-TABLE). If your list gets long, performance will be better with `LEFT JOIN` / `IS NULL`. I added demo code to my answer. – Erwin Brandstetter Jun 17 '13 at 03:53
  • You can use `WHERE q.code = ANY($1)` to get `q.code IN [...]` behaviour – Eugene Kulabuhov Jun 12 '20 at 15:39