Question 272019 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres table functions

Hi I have some questions regarding table functions in PostgreSQL

From https://www.postgresql.org/docs/16/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS

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?

Thanks

Tags:

2

Answers


  1. 0

    1. The query does nothing useful and returns all table rows. It is just an example.

    2. The function will be called for each row of the table. You can use EXPLAIN to see how PostgreSQL processes the query.

    Login or Signup to reply.
  2. 0

    1.

    including subtle things

    The query’s is useless – other than to demonstrate syntax and functionality. It query 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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search