Search code examples
sqlpostgresqlsyntaxset-returning-functions

Scope and functionality of table functions

I have some questions regarding the example for table functions in PostgreSQL manual:

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 foo
    WHERE foosubid IN (
                        SELECT foosubid
                        FROM getfoo(foo.fooid) z
                        WHERE z.fooid = foo.fooid
                      );

  1. How is this particular nested SELECT statement different, including subtle things, from SELECT * FROM foo? How is this particular statement useful?

  2. How is getfoo(foo.fooid) able to know the value of foo.fooid? Does a function always iterate through all values when we pass in an argument like table.column?

Solution

  • 1.

    including subtle things

    The query's is useless - other than to demonstrate syntax and functionality. It burns down to just SELECT * FROM foo - except that it eliminates rows with null values in fooid or foosubid. So the actual simple equivalent is:

    SELECT * FROM foo
WHERE  fooid IS NOT NULL
AND    foosubid IS NOT NULL;

    fiddle

    If the table would have NOT NULL constraints (incl. the implicit constraint of a PK), there would be no effective difference other than performance.

    2.

    The scope of the subquery in the IN expression extends to the main query, where foo is listed in the FROM clause. Effectively an implicit LATERAL subquery, where the subquery is executed once for every row in the main query. The manual:

    Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)

    Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case.

    See: