CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4sql-viewset-returning-functions
Drux
Drux

Reputation: 12650

"ERROR: column ... specified more than once" in VIEW definition

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;

Upvotes: 1

Views: 6515

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

I suggest a LATERAL join in the SELECT query:

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:

Upvotes: 1

Related Questions