how to refer timestamp variable in pgsql function having select statement

Question

Hi Am trying to write a pgsql function which performs copy of table data to a csv file and am having difficulty using the variable value inside the function as below:

CREATE OR REPLACE  FUNCTION test3() RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
_currenttime timestamp := now();
BEGIN
copy (SELECT * FROM table1 WHERE createdtime < _currenttime - INTERVAL '1 days') TO '/tmp/table1.csv';
END
$$;

I get error while accessing the function:

select test3();

ERROR: column "_currenttime" does not exist LINE 1: ...py (SELECT * FROM table1 WHERE createdtime < _currentti... ^ QUERY: copy (SELECT * FROM bpminstance WHERE createdtime < _currenttime - INTERVAL '1 days') TO '/tmp/table1.csv'

Request your kind help.

Answer 1

CREATE OR REPLACE  FUNCTION test3() RETURNS void
    LANGUAGE plpgsql
    AS $$
 DECLARE
_currenttime timestamp;
qry text;
BEGIN
_currenttime := NOW() - interval '1 day';
select 'copy (SELECT * FROM table1 WHERE createdtime < '''||_currenttime||''') TO ''/tmp/table1.csv''' into qry;
execute qry;
--codes
END
$$;