CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Drux
Drux

Reputation: 12650

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

Upvotes: 0

Views: 2248

Answers (1)

Abelisto
Abelisto

Reputation: 15614

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

Upvotes: 1

Related Questions