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?
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:
Declare a new composite type containing the columns you want to return, or
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.
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.