Pass array literal to PostgreSQL function

by

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.

More Related Contents:

  1. Cannot simply use PostgreSQL table name (“relation does not exist”)
  2. Best way to get result count before LIMIT was applied
  3. How to use php array with sql IN operator? [duplicate]
  4. Using value of a column as index in results using PDO
  5. How to pass custom type array to Postgres function
  6. Convert PostgreSQL array to PHP array
  7. Query database then email posts from where user is subbed to
  8. Display multidimensional array in php
  9. Sum up array values [closed]
  10. Is there any array function by which I can get the expected result? [duplicate]
  11. Convert php array to Javascript
  12. Convert a PHP object to an associative array
  13. PHP in_array() / array_search() odd behaviour
  14. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  15. Check if value exists in Postgres array
  16. How to check whether an array is empty using PHP?
  17. Convert array of single-element arrays to one a dimensional array
  18. PHP list all files in directory [duplicate]
  19. PHP – Merging two arrays into one array (also Remove Duplicates)
  20. CakePHP find method with JOIN
  21. PHP Random Shuffle Array Maintaining Key => Value
  22. Make var_dump look pretty [duplicate]
  23. PHP – Merge two arrays (same-length) into one associative?
  24. Merge row data from multiple arrays
  25. PHP – Convert multidimensional array to 2D array with dot notation keys
  26. Loop a multidimensional array and only print two specific column values per row
  27. Group rows in an associative array of associative arrays by column value and preserve the original first level keys
  28. Simple PHP SQL login troubleshooting
  29. Find number which is greater than or equal to N in an array
  30. Check if all values in array are the same

Leave a Comment