Pass multiple values in single parameter

by

VARIADIC

Like @mu provided, VARIADIC is your friend. One more important detail:

You can also call a function using a VARIADIC parameter with an array type directly. Add the key word VARIADIC in the function call:

SELECT * FROM  f_test(VARIADIC '{1, 2, 3}'::int[]);

is equivalent to:

SELECT * FROM  f_test(1, 2, 3);

Other advice

In Postgres 9.1 or later right() with a negative length is faster and simpler to trim leading characters from a string:

right(j.status, -2)

is equivalent to:

substring(j.status, 3, char_length(jobs.status))

You have j."DeleteFlag" as well as j.DeleteFlag (without double quotes) in your query. This is probably incorrect. See:

"DeleteFlag" = '0' indicates another problem. Unlike other RDBMS, Postgres properly supports the boolean data type. If the flag holds boolean data (true / false / NULL) use the boolean type. A character type like text would be inappropriate / inefficient.

Proper function

You don’t need PL/pgSQL here. You can use a simpler SQL function:

CREATE OR REPLACE FUNCTION f_test(VARIADIC int[])
  RETURNS TABLE (id int, reference int, job_title text, status text)
  LANGUAGE sql AS
$func$
   SELECT j.id, j.reference, j.job_title
        , ltrim(right(j.status, -2)) AS status
   FROM   company c
   JOIN   job     j USING (id)
   WHERE  c.active
   AND    NOT c.delete_flag
   AND    NOT j.delete_flag
   AND   (j.id = ANY($1) OR '{-1}'::int[] = $1)
   ORDER  BY j.job_title
$func$;

db<>fiddle here
Old sqlfiddle

More Related Contents:

  1. Difference between language sql and language plpgsql in PostgreSQL functions
  2. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  3. Return setof record (virtual table) from function
  4. Refactor a PL/pgSQL function to return the output of various SELECT queries
  5. Is SELECT or INSERT in a function prone to race conditions?
  6. How to return result of a SELECT inside a function in PostgreSQL?
  7. GROUP BY and aggregate sequential numeric values
  8. PL/pgSQL checking if a row exists
  9. PostgreSQL parameterized Order By / Limit in table function
  10. PostgreSQL IF statement
  11. Call a set-returning function with an array argument multiple times
  12. Record returned from function has columns concatenated
  13. Postgres Dynamic Query Function
  14. Window Functions or Common Table Expressions: count previous rows within range
  15. DROP FUNCTION without knowing the number/type of parameters?
  16. Postgres function returning table not returning data in columns
  17. Return multiple columns and rows from a function PostgreSQL instead of record
  18. COPY with dynamic file name
  19. Functions with variable number of input parameters
  20. pseudo_encrypt() function in plpgsql that takes bigint
  21. How to perform a select query in a DO block?
  22. Function with SQL query has no destination for result data
  23. Postgres trigger after insert accessing NEW
  24. Paging with Oracle
  25. Multiple CTE in single query
  26. Return pre-UPDATE column values using SQL only
  27. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  28. Selecting data into a Postgres array
  29. Postgres FOR LOOP
  30. How to use SQL LIKE condition with multiple values in PostgreSQL?

Leave a Comment