CREATE VIEW指定的列名称多于列 [英] CREATE VIEW specifies more column names than columns
问题描述
如果我在PostgreSQL 9.4.8中运行以下语句,则会收到此错误消息:
If I run the following statements in PostgreSQL 9.4.8, I get this error message:
CREATE VIEW指定的列名比列。
CREATE VIEW specifies more column names than columns.
但是为什么呢? f1
是否不返回包含5列的表,并且 v1
也不应该有5列吗?另外,如果我从第一个 SELECT
语句中删除强制类型转换,则会收到以下错误消息:
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:
最后一条语句返回未知值,而不是第1列的字符。
Final statement returns unknown instead of character varying at column 1.
但是为什么呢?正确的类型 VARCHAR(20)
从 RETURNS
中可以知道,为什么没有隐式的字符串转换,例如'a'
?
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);
推荐答案
考虑简单的示例:
postgres=# create function foofunc() returns table(a int, b text) language sql as $$ select 1, 'a'::text $$;
postgres=# select foofunc();
╔═════════╗
║ foofunc ║
╠═════════╣
║ (1,a) ║
╚═════════╝
当列/变量上下文它将返回指定的返回类型的单个值。这是错误的根源:视图的 select
仅返回一列。
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);
这篇关于CREATE VIEW指定的列名称多于列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!