主页 / dba / 问题 / 230685
Accepted
Randomize
Asked: 2019-02-26 09:43:37 +0800 CST

Postgres 在函数内返回“列引用 '<column_name>' 不明确”消息，即使使用别名也是如此

  • 772

我有这样的功能：

CREATE OR REPLACE FUNCTION test() RETURNS 
TABLE(my_date date, gates smallint, area_type character(1), 
      area_sizes float[], area_canvases float[]) AS $$
BEGIN
   return query 
   select coalesce(s.my_date, r.my_date) as my_date,  coalesce(s.gates, r.gates) as gates, coalesce(s.area_type, r.area_type) as area_type, s.area_sizes, r.area_canvases
   from (select my_date, gates, area_type, area_sizes from table1 where area_type = 'X') s
   full join (select my_date, gates, area_type, area_canvases from table2 where area_type = 'Y') r
   on r.my_date = s.my_date;
END $$ language plpgsql;

当我运行它时select * from test();，我得到的是：

ERROR: column reference "my_date" is ambiguous
LINE 4: my_date,

如果未包含在函数中的查询可以正常工作，但我无法使其在其中工作。任何想法？

postgresql errors

1 个回答

  1. Best Answer

    计划者无法理解您在那里提到的 my_date 。参数中的一个或 table1（或 table2）中的一个。正确的语法应该如下所示；

    CREATE OR REPLACE FUNCTION test ()
RETURNS TABLE (
  my_date date, gates smallint, area_type character (1), 
  area_sizes float[], area_canvases float[]
)
AS $$
BEGIN
    RETURN query
    SELECT
        coalesce(s.my_date, r.my_date) AS my_date,
        coalesce(s.gates, r.gates) AS gates,
        coalesce(s.area_type, r.area_type) AS area_type,
        s.area_sizes,
        r.area_canvases
    FROM (
        SELECT
            t1.my_date,
            t1.gates,
            t1.area_type,
            t1.area_sizes
        FROM
            table1 t1
        WHERE
            t1.area_type = 'X') s
    FULL JOIN (
        SELECT
            t2.my_date, 
            t2.gates, 
            t2.area_type, 
            t2.area_canvases
        FROM
            table2 t2
        WHERE
            t2.area_type = 'Y') r ON r.my_date = s.my_date;
END
$$
LANGUAGE plpgsql;
    • 0

相关问题