This is a follow-up question to an earlier one. I have a stored function
f1 that takes two arguments returns a table with 5 columns; for now the returned values are constant, later they will be calculated from the arguments.
I also have a table
t1 with two columns that correspond in type to
I would now like to define a view
v1 that contains the union of all rows returned from
f1 for all argument pairs stored in
t1. For the given example values the result should be:
| 2 | 3 | a | b | 1 | c | d |
| 4 | 5 | a | b | 1 | c | d |
If the first two columns are stripped of, that would be fine as well. Notice that
f1 could return several rows for certain argument values.
I've tried the following statement, but it gives me this error message:
ERROR: column "c4" specified more than once
CREATE VIEW v1 (c1, c2, c3, c4, c5)
AS SELECT * FROM
(SELECT c1, c2 FROM t1) AS x,
f1 (x.c1, x.c2);
What am I doing wrong?
Here are the preceding statements to set the example up:
CREATE OR REPLACE FUNCTION f1 (a1 INTEGER, a2 INTEGER)
RETURNS TABLE (c1 VARCHAR(20), c2 VARCHAR(20), c3 INTEGER, c4 VARCHAR(20), c5 VARCHAR(128))
SELECT 'a'::VARCHAR(20), 'b'::VARCHAR(20), 1::INTEGER, 'c'::VARCHAR(20), 'd'::VARCHAR(128);
$$ LANGUAGE SQL;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 INTEGER, c2 INTEGER);
INSERT INTO t1 (c1, c2)
VALUES (2,3), (4,5);
DROP VIEW IF EXISTS v1;
I suggest a
LATERAL join in the
CREATE VIEW v1 AS SELECT f.* FROM t1 , f1 (t1.c1, t1.c2) AS f; -- implicit CROSS JOIN LATERAL
Since the column names defined in the function definition already match what you want, you can also drop the column names from view definition.
Creating a view works with early binding anyway. Meaning, only columns selected at creation time are included in the view. If you later change the function definition to return an additional column, then that's not included in the view. (If you remove or rename columns, you break the view.)
You could also include the set-returning function
f1(..) in the
SELECT list, to almost the same effect. The differences:
Set-returning functions in the
SELECT list violate the SQL standard and are frowned upon by some. Also not portable to some other RDBMS. Since Postgres introduced (standard SQL)
LATERAL with Postgres 9.3, that's generally preferable.
A set-returning function in the
SELECT list (effectively a correlated subquery) is the equivalent of
LEFT JOIN LATERAL ... ON true, i.e. it preserves all rows of
t1, even where the function does not return any rows.
CROSS JOIN LATERAL like above removes rows where
f1() does not return rows.
You can decompose the well-defined row type returned from the function with
SELECT (f1(...)).*, ..., but the function may be evaluated repeatedly for each column in the return type instead of once only.
Related and more details:
Split function-returned record into multiple columns
What is the difference between LATERAL and a subquery in PostgreSQL?