Kumaro Kumaro - 14 days ago 6
SQL Question

Return a single row but with additional columns in Postgresql

I want to create a function which returns a single row of a table, but some additional columns from other tables it can be joint with. How can I do that? Should I declare a new type? Or should I return the table type?

Answer

You cannot return value of the table's composite type, because then you would not be able to add the additional columns.

You could either:

  1. Declare a new composite type containing the columns you want to return, or

  2. Return a RECORD type, which doesn't have any fixed structure. In that scenario you would be returning the resulting row of a query which extracted the row of the table joined to the other tables.

Based on the tiny amount of information you have provided, a RECORD would be easiest.

See:

UPDATE: After testing using a record type, they turn out to be somewhat inconvenient to use. Consider this example:

CREATE TABLE a(id SERIAL PRIMARY KEY, foo TEXT, bar INT);
CREATE TABLE b(id SERIAL PRIMARY KEY, aid INT, baz TEXT);
INSERT INTO a(foo,bar) VALUES('apples', 25);
INSERT INTO b(aid, baz) VALUES(1, 'bananas');

CREATE OR REPLACE FUNCTION TEST(p_id INT) RETURNS SETOF RECORD AS $$
BEGIN
    RETURN QUERY SELECT a.id,foo,bar,baz FROM a LEFT JOIN b ON a.id=b.aid WHERE A.ID=p_id;
END;
$$ LANGUAGE plpgsql;

If you naively try to use it like this:

psql=> SELECT * FROM TEST(1);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM TEST(1);
                      ^

In fact, every time you want to call it, you would have to specify a column definition list like this:

psql=> SELECT * FROM TEST(1) AS test(id int, foo text, bar int, baz text);
 id |  foo   | bar |   baz
----+--------+-----+---------
  1 | apples |  25 | bananas
(1 row)

So, if that's your intended use case, then I recommend following the approach outlined by Craig.