0

If I run the following statements in PostgreSQL 9.4.8, I get this error message:

CREATE VIEW specifies more column names than columns.

But why? Doesn't f1 return a table with 5 columns and shouldn't v1 have 5 columns as well? Also, If I remove the casts from the first SELECT statement, I get this error message:

Final statement returns unknown instead of character varying at column 1.

But why? The correct type VARCHAR(20) is known from RETURNS, so why is there no implicit cast of strings such as 'a'?

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;

CREATE VIEW v1 (c1, c2, c3, c4, c5)
AS SELECT f1 (1, 2);
Drux
  • 11,992
  • 13
  • 66
  • 116
  • 2
    `... select * from f1 (1, 2);` Your variant returns the single column of the `record` type (just try it without creating a view). – Abelisto Aug 05 '16 at 16:24
  • @Abelisto Excellent, if you want to turn this comment into an answer, I shall accept it. Thx in any case. – Drux Aug 05 '16 at 16:57

1 Answers1

1

Consider the simple example:

postgres=# create function foofunc() returns table(a int, b text) language sql as $$ select 1, 'a'::text $$;
postgres=# select foofunc();
╔═════════╗
║ foofunc ║
╠═════════╣
║ (1,a)   ║
╚═════════╝

When a function called in the column/variable context it returns the single value of the returning type specified. Here is the source of the error: the view's select returns only one column.

However if function called in the table context then it returns the values like a true table:

postgres=# select * from foofunc();
╔═══╤═══╗
║ a │ b ║
╠═══╪═══╣
║ 1 │ a ║
╚═══╧═══╝

So you should to use the second approach when you creating the view:

CREATE VIEW v1 (c1, c2, c3, c4, c5) AS
  SELECT * FROM f1 (1, 2);
Abelisto
  • 14,826
  • 2
  • 33
  • 41
  • Thx & FYI: I have posted a [follow-up](http://stackoverflow.com/questions/38794976/error-column-specified-more-than-once) question. – Drux Aug 05 '16 at 17:38