Table functions are functions that produce a set of rows, made up of
either base (scalar) data types, or composite (multi-column) data types.
They are used like a table, view, or subselect in the FROM
clause of a query. Columns returned by table functions may be included in
SELECT, JOIN, or WHERE clauses in the
same manner as a table, view, or subselect column.
If a table function returns a base data type, the single result column
is named for the function. If the function returns a composite type, the
result columns get the same names as the individual attributes of the type.
A table function may be aliased in the FROM clause, but it also
may be left unaliased. If a function is used in the FROM clause with no
alias, the function name is used as the relation name.
Table functions work wherever tables do in SELECT statements.
For example
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
SELECT * FROM foo
WHERE foosubid in (select foosubid from getfoo(foo.fooid) z
where z.fooid = foo.fooid);
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
are all valid statements.
In some cases it is useful to define table functions that can return
different column sets depending on how they are invoked. To support this,
the table function can be declared as returning the pseudo-type
record. When such a function is used in a query, the expected
row structure must be specified in the query itself, so that the system
can know how to parse and plan the query. Consider this example:
SELECT *
FROM dblink('dbname=template1', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
The dblink function executes a remote query (see
contrib/dblink). It is declared to return record
since it might be used for any kind of query. The actual column set
must be specified in the calling query so that the parser knows, for
example, what * should expand to.
