CODEWITHSUNDEEP
databasepostgresql
npad
npad

Reputation: 5046

PostgreSQL function return type

I'm trying to define a function along the lines of:

CREATE FUNCTION myfunction(list_of_ids integer[]) RETURNS record AS $$
  SELECT a.col1, b.col2 FROM a, b WHERE col1 = ANY($1);
$$ LANGUAGE 'sql'

However postgres doesn't seem happy with "record" as the return type, giving me this error when I try to execute the function:

ERROR:  a column definition list is required for functions returning "record"

Looking through the docs though I can't work out how to supply this column definition list.

Upvotes: 0

Views: 16148

Answers (2)

npad
npad

Reputation: 5046

I ended up using a prepared query for this instead - all I really wanted was to ensure that the overhead of parsing the query and generating the query plan each time was eliminated, so a prepared statement worked fine for that.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656251

First of all, your query seems off: no JOIN condition for tables a and b? You don't want a Cartesian product, do you? Try something like:

SELECT a.col1, b.col2
FROM   a
JOIN   b ON <some condition like b.a_id = a.a_id>
WHERE  col1 = ANY($1);

Next, use a return type for the function so you don't have to provide a column definition list for every call. In the example I make use of RETURNS TABLE. Read about more ways in the manual. Could look like this:

CREATE FUNCTION myfunction(list_of_ids integer[])
 RETURNS TABLE (
  col1 text -- has to match the type of the column a.col1 that is returned.
 ,col2 text -- match type of a.col2
) AS
$$
  SELECT a.col1, b.col2
  FROM   a
  JOIN   b ON <some condition like b.a_id = a.a_id>
  WHERE  col1 = ANY($1);
$$
  LANGUAGE sql;

The single quotes around 'sql' are just noise. It can function as a key word.

Now you can call:

SELECT * FROM  myfunction('{11,23,6}'::integer[]);

Upvotes: 4

Related Questions