Drux Drux - 3 months ago 4
SQL Question

ERROR: column ... specified more than once

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
f1
's arguments.

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))
AS $$
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;

Answer

I suggest a LATERAL join in the SELECT query:

CREATE VIEW v1 AS
SELECT f.* FROM
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 work 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 in the SELECT list, to almost the same effect. The differences: set-returning functions in the SELECT list violate the SQL standard and are generally discouraged. Since Postgres introduces LATERAL with Postgres 9.3, that's generally preferable.

And a correlated subquery is the equivalent of LEFT JOIN LATERAL ... ON true, i.e. it preserves rows of t1 where the function does not return any rows. Details:

Comments