Postgres date value dynamic inserting-CodePudding

I am scratching my head trying to fix the following:

create or replace procedure my_schema.Test()
as $$

declare
    today date = now();

begin   
    drop table if exists my_schema.tst_table;
    create table  my_schema.tst_table (
        todays_date varchar     );
    
    execute('
        insert into my_schema.tst_table 
        values (' || today || ')    
    ');

end;
$$
language plpgsql;

Basically I am trying to dynamically insert the current date into a table which will be used in later steps.

The issue I am facing is that due to the today variable looking like '2022-02-11', and because I am inserting the record dynamically, postgres is interpreting the "-" as a minus sign and inserting the value 2009 into my table.

Does anyone have a workaround for this?

CodePudding user response：

Don't concatenate input values into dynamic SQL. And never store date values in a varchar column:

create or replace procedure my_schema.Test()
as $$
declare
    today date := current_date;
begin   
    drop table if exists my_schema.tst_table;
    create table  my_schema.tst_table 
    (
      todays_date date
    );
    
    execute('
        insert into my_schema.tst_table 
        values ($1)    
    ') using today;
end;
$$
language plpgsql;

But creating a table just to store the value of current_date seems a bit of an overkill.

CodePudding user response：

You can use the cast operator to force the value conversion to VARCHAR datatype:

execute('
    insert into my_schema.tst_table 
    values ('today'::VARCHAR)    
');