It's a function or table in this later join case?

user_10196687 0
Viewed 24

It is often particularly handy to LEFT JOIN to a LATERAL subquery, so that source rows will appear in the result even if the LATERAL subquery produces no rows for them. For example, if get_product_names() returns the names of products made by a manufacturer, but some manufacturers in our table currently produce no products, we could find out which ones those are like this:

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;

All contents extract from PostgreSQL manual. LINK Now I finally probably get what does LATERAL mean. In this case,

Overall I am Not sure get_product_names is a table or function. The following is my understanding.
A: get_product_names(m.id) is a function, and using m.id as a input parameter returns a table. The return table alias as pname. Overall it's a table m join a null (where condition) table.

B: get_product_names is a table, table m left join table get_product_names on m.id. pname is alias for get_product_names. Overall it's a table m join a null (where condition) table.

postgresql
1 Answers

get_product_names is a table function (also known as set returning function or SRF in PostgreSQL slang). Such a function does not necessarily return a single result row, but arbitrarily many rows.

Since the result of such a function is a table, you typically use it in SQL statements where you would use a table, that is in the FROM clause.

A simple example is

SELECT * FROM generate_series(1, 5);

 generate_series 
-----------------
               1
               2
               3
               4
               5
(5 rows)

You can also use normal functions in this way, they are then treated as a table function that returns exactly one row.

user_64643080
Related