时间戳

首页 > 解决方案 > Postgres 函数不明确的列引用

postgresql - Postgres 函数不明确的列引用

问题描述

从同一个模板中获得了两个功能：

CREATE OR REPLACE FUNCTION public.rdxi(
    mydate date DEFAULT (
    (
    'now'::text)::date - '1 day'::interval))
RETURNS TABLE("Date" date, "Nom" character varying, "Capacité (TB)" numeric, "Utilisé (TB)" numeric, "Charge (%)" text, "Données protégées (TB)" numeric, "Ratio" numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY
SELECT "rDxi"."Date", "rDxi"."Nom", "rDxi"."Capacité réelle en TB", "rDxi"."Utilisé en TB", "rDxi"."Charge", "rDxi"."Données protégées en TB", "rDxi"."Ratio"
 from "rDxi" where "rDxi"."Date" = mydate;
END
$BODY$;

和

CREATE OR REPLACE FUNCTION public."rPower-Chassis-Ram"(
    mydate date DEFAULT (
    (
    'now'::text)::date - '1 day'::interval))
RETURNS TABLE("Date" date, "Id" character varying, "Nom" character varying, "Cpu type" text, "Ram totale (GB)" numeric, "Ram libre (GB)" numeric, "Charge (%)" numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY
SELECT "rPower-Chassis-Ram"."Date", "rPower-Chassis-Ram"."Id", "rPower-Chassis-Ram"."Nom", "rPower-Chassis-Ram"."Type Cpu", "rPower-Chassis-Ram"."Ram Totale", "rPower-Chassis-Ram"."Ram_Libre",
"rPower-Chassis-Ram"."Charge"
 from "rPower-Chassis-Ram" where "rPower-Chassis-Ram"."Date" = mydate;
END
$BODY$;

要求第一个时没问题，但要求第二个时我收到以下错误消息：

ERROR:  column reference "rPower-Chassis-Ram.Date" is ambiguous
LINE 1: SELECT "rPower-Chassis-Ram"."Date", "rPower-Chassis-Ram"."Id...
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT "rPower-Chassis-Ram"."Date", "rPower-Chassis-Ram"."Id", "rPower-Chassis-Ram"."Nom", "rPower-Chassis-Ram"."Type Cpu", "rPower-Chassis-Ram"."Ram Totale", "rPower-Chassis-Ram"."Ram_Libre",
"rPower-Chassis-Ram"."Charge"
 from "rPower-Chassis-Ram" where "rPower-Chassis-Ram"."Date" = mydate
CONTEXT:  PL/pgSQL function "rPower-Chassis-Ram"(date) line 3 at RETURN QUERY
État SQL :42702

不知道在哪里调查，尝试更改函数的名称（替换 - 由_），尝试使用没有特殊字符的通用名称重命名，但没有办法......

标签： postgresqlfunction

解决方案

名称冲突发生在funcrion 参数和列名之间。

最简单的解决方案是避免名称冲突，例如通过调用OUT参数p_date。

或者，您可以使用表别名：

SELECT t."Date"
FROM aschema."SillyTableName" AS t
...

推荐阅读