Drux Drux - 4 months ago 17
SQL Question

CREATE VIEW specifies more column names than columns

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

Answer

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