PostgreSQL尝试在函数中使用执行格式，但在合并时给出字符串格式时出现未找到列错误

问题描述：

我正在尝试创建一个函数并指定日期格式，但是该日期格式被用作列名，因为在格式内部某种程度上它不能被表示为字符串.我尝试了％s，引用了缩进和其他所有内容，但是没有用.下面是我的代码和我得到的错误

I'm trying to create a function and specify a date format but the date format is being taken as a column name because somehow inside format it's not being able to be represented as a string. I have tried %s, quote indent and everything else but doesnt work. Below is my code and the error I'm getting

drop function if exists foo(_t text);
create or replace function foo(_t text)
returns TABLE(Stage_ID bigint,Date varchar) as
$func$
begin
    return query

        execute format('Select Stage_ID,Date
        from table
        where to_date(Date, "YYYY-MM-DD")==%I',_t);
end
$func$  language plpgsql;

select * from foo('2010-01-01');

错误

ERROR:  column "YYYY-MM-DD" does not exist
LINE:         where TO_DATE(Date, "YYYY-MM-DD") =  p...

答

这可能会满足您的需求:

This might do what you are looking for:

CREATE OR REPLACE FUNCTION foo(_t text)
  RETURNS TABLE (Stage_ID bigint, Date varchar) AS
$func$
   SELECT t.Stage_ID, t.Date
   FROM   tbl t
   WHERE  t.Date = _t::date;
$func$  LANGUAGE sql;

表达式where to_date(Date, "YYYY-MM-DD")==%I',_t);以多种方式向后.
- The expression where to_date(Date, "YYYY-MM-DD")==%I',_t); is backwards in multiple ways.
  - 值的单引号:'YYYY-MM-DD'.
  - 运算符是=，而不是==.
  - 好像您真的想要t.Date = to_date(_t, 'YYYY-MM-DD')
  - _t以标准ISO格式'YYYY-MM-DD'表示时，而是强制转换为t.Date = _t::date.
  - Single quotes for values: 'YYYY-MM-DD'.
  - The operator is =, not ==.
  - Seems like you really want t.Date = to_date(_t, 'YYYY-MM-DD')
  - And while _t is in standard ISO form 'YYYY-MM-DD', rather just cast instead: t.Date = _t::date.
  输出列名称在函数体内是可见的.具有相同名称的表限定列.更好的是，避免像这样开始命名冲突！参见:
  
  Output column names are visible inside the function body. Table-qualify column of the same name. Better yet, avoid naming conflicts like that to begin with! See:
  - 如何在PostgreSQL的函数中返回SELECT的结果?
  不需要使用EXECUTE的动态SQL.传递数据值仅适用于普通SQL.
  
  No need for dynamic SQL with EXECUTE. Passing a data value works just fin with plain SQL.
  
  此外:请勿使用诸如日期"之类的基本类型名称作为标识符.坚持使用合法的小写字母标识符.相关:
  
  Aside: don't use basic type names like "date" as identifier. Stick to legal, lower case identifiers. Related:
  - PostgreSQL列名是否区分大小写?

PostgreSQL尝试在函数中使用执行格式，但在合并时给出字符串格式时出现未找到列错误

相关推荐