“错误：列...指定了多次”在VIEW定义中 [英] "ERROR: column ... specified more than once" in VIEW definition

问题描述

这是对早期版本的后续问题。我有一个存储函数 f1 ，它带有两个参数，返回一个包含5列的表；现在返回的值是常量，以后将根据参数计算得出。

我还有一个表 t1 ，其中两列的类型与 f1 的参数相对应。

我现在要定义一个视图 v1 包含从 f1 返回的所有行的并集，该行存储在 t1中。对于给定的示例值，结果应为：

  + --- + --- + --- +- + --- + --- + --- + 
 | 2 | 3 |一个| b | 1 | c | d | 
 + --- + --- + --- + --- + --- + --- + --- + 
 | 4 | 5 |一个| b | 1 | c | d | 
 + --- + --- + --- + --- + --- + --- +-++ 
  

如果除去前两列，那也没问题。请注意， f1 可能会为某些参数值返回几行。

我尝试了以下语句，但是它给我这个错误消息：

 错误：列 c4指定了多次
  

 创建视图v1（c1，c2 ，c3，c4，c5）
 AS SELECT * FROM 
（SELECT c1，c2 FROM t1）AS x，
 f1（x.c1，x.c2）; 
  

我在做什么错了？

以下是设置示例的前面的语句：

 创建或替换功能f1（a1整数，a2整数）
返回表（c1 VARCHAR（20），c2 VARCHAR（20），c3 INTEGER，c4 VARCHAR（20），c5 VARCHAR（128））
 AS $$ 
选择'a':: VARCHAR（ 20），'b':: VARCHAR（20），1 :: INTEGER，'c':: VARCHAR（20），'d':: VARCHAR（128）; 
 $语言SQL；如果存在t1，则
 
下降表； 
 CREATE TABLE t1（c1 INTEGER，c2 INTEGER）; 
插入t1（c1，c2）
值（2,3），（4,5）; 
 
如果存在v1，则直接删除视图； 
  

解决方案

我建议使用 LATERAL 加入 SELECT 查询：

 创建查看v1 AS 
选择f。* 
从t1 
，f1（t1.c1，t1.c2）AS f; -隐式CROSS JOIN LATERAL 
  

由于函数定义中定义的列名已经与您想要的匹配，您还可以从视图定义中删除列名称。

无论如何，创建视图都可以使用 early binding 。意味着，视图中仅包含在创建时选择的列。如果您以后更改函数定义以返回附加列，则该视图中将包含不。 （如果删除或重命名列，则会破坏视图。）

您可以还包含返回设置的函数 SELECT 列表中的> f1（..），几乎达到相同的效果。差异：

• SELECT 列表中的集返回函数被违反SQL标准，并被某些人皱眉。也不能移植到其他RDBMS。由于Postgres在Postgres 9.3中引入了（标准SQL） LATERAL ，因此通常更可取。




• SELECT 列表中的返回函数（实际上是相关子查询）等效于 LEFT JOIN LATERAL ... ON true ，即即使该函数不返回任何行，它也会保留 t1 的所有行。 像这样的交叉联接 删除行，其中 f1（）不返回行。 / p>




• 您可以使用 SELECT（f1（。 。））。*，... ，但是可以为返回类型中的每一列重复评估该函数，而不是仅一次。

相关和更多详细信息：

• 将函数返回的记录拆分为多列




• LATERAL和PostgreSQL中的子查询有什么区别？

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;

解决方案

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:

• Split function-returned record into multiple columns

• What is the difference between LATERAL and a subquery in PostgreSQL?

这篇关于“错误：列...指定了多次”在VIEW定义中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！