Postgresql trying to use execute format in a function but getting column not found error when giving string format in coalesce

Question

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

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

Answer 1

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;

• The expression where to_date(Date, "YYYY-MM-DD")==%I',_t); is backwards in multiple ways.

  • 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:

  • How to return result of a SELECT inside a function in PostgreSQL?

• No need for dynamic SQL with EXECUTE. Passing a data value works just fin with plain SQL.

• No need for plpgsql. The simple query does not require any procedural functionality. LANGUAGE sql does the job - if you need a function at all, plain SQL would seem just fine for the job.

Aside: don't use basic type names like "date" as identifier. Stick to legal, lower case identifiers. Related:

• Are PostgreSQL column names case-sensitive?