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.
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
$$;