主页 / dba / 问题 / 105831
Accepted
Erwin Brandstetter
Asked: 2015-07-03 15:54:25 +0800 CST

函数参数与使用 USING 子句的 JOIN 结果之间的命名冲突

  • 772

鉴于当前 Postgres 9.4 中的此设置（来自此相关问题）：

CREATE TABLE foo (ts, foo) AS 
VALUES (1, 'A')  -- int, text
     , (7, 'B');

CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
     , (5, 'D')
     , (9, 'E');

db<>fiddle here（也来自上一个问题）。

我用 a 写了SELECT一个FULL JOIN来实现引用问题的目标。简化：

SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);

根据规范，处理该列的正确方法ts是没有表限定。任何一个输入值 (f.ts或b.ts) 都可以为 NULL。该USING子句创建了一些奇怪的情况：引入了一个实际上不存在于输入中的“输入”列。到目前为止如此优雅。

我把它放在一个 plpgsql 函数中。为了方便（或要求），我希望表函数的结果具有相同的列名。所以我们必须避免相同的列名和函数参数之间的命名冲突。最好通过选择不同的名称来避免，但我们在这里：

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

大胆强调突出问题。我不能ts像以前那样在没有表限定的情况下使用，因为 plpgsql 会引发异常（不是绝对必要的，但在大多数情况下可能很有用）：

ERROR:  column reference "ts" is ambiguous
LINE 1: SELECT ts, f.foo, b.bar
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

我知道我可以使用不同的名称或子查询或使用其他函数。但我想知道是否有办法引用该列。我不能使用表限定。人们会认为应该有一种方法。
在那儿？

postgresql join

2 个回答

  1. Best Answer

    根据文档PL/pgSQL Under the Hood，您可以在创建函数之前或在函数定义开始时使用配置参数plpgsql.variable_conflict，声明您希望如何解决此类冲突。
    3 种可能的设置是error（默认）use_variable和use_column：

    CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column             -- how to resolve conflicts
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- do something
      RETURN NEXT;
   END LOOP;
END
$func$;
    • 23

  2. Postgres 14添加了 SQL 语法来解决这个问题。现在您可以附加一个AS子句来为列表中合并的列声明一个新的表别名USING：

    
CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT merged.ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts) AS merged
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

    手册：

    USING ( join_column [, ...] ) [ AS join_using_alias ]

    [...]

    如果join_using_alias指定了名称，它会为连接列提供表别名。USING只有子句中列出的连接列 可以通过该名称寻址。与常规别名不同，这不会对查询的其余部分隐藏连接表的名称。与常规别名不同的是，您不能编写列别名列表——连接列的输出名称与它们在USING列表中显示的名称相同。

    • 4

相关问题