I want to create a function that returns all columns from let's say 2 tables that are joined in the function's query. If that was only one table I could simply use table's name to define a return type. For more tables the only way I know to go about it would be to define each column separately which becomes a bit complicated when I have a lot of columns in the tables. Is there a way to do that simpler?
2 Answers
You may create a view and use it as a type:
CREATE VIEW viewname AS select * from table1, table2, table3;
Then this function declaration would work:
CREATE FUNCTION multiple_tables(...) RETURNS SETOF viewname AS
$$
return select * from table1 join table2 on (...) join table3 on (...) WHERE ...;
$$ language sql;
RETURNS viewname without SETOF is possible too.
The function will depend on viewname so that the view cannot be later structurally modified without dropping the function first.
Comments
There is no dedicated feature for that.
You either list all columns in a RETURNS TABLE clause:
CREATE FUNCTION
...
RETURNS TABLE (col1 type1, col2 type2, ...)
...
Or you create a TYPE and use RETURNS SETOF ..
CREATE TAPE mytype (col1 type1, col2 type2, ...);
...
RETURNS SETOF mytype
...
answered Oct 20, 2014 at 13:04
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
RETURNS TABLE) which has composite types as fields. Accessing table columns will be longer, but you don't have to copy columns (and their types) + changes in the original table's structure will be immediately reflected + duplicate column names are allowed this way.