3

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.

Vivek S.
  • 19,945
  • 7
  • 68
  • 85
Rajesh Chandrashekar
  • 41
  • 1
  • 1
  • 8
  • in the error output bpminstance refers to table1 – Rajesh Chandrashekar Oct 12 '15 at 11:23
  • try `copy (SELECT * FROM table1 WHERE createdtime < NOW() - interval '1 day') TO '/tmp/table1.csv';` – Vivek S. Oct 12 '15 at 11:23
  • Thanks, I tried that, but i have other statements followed by this, so i need to refer this variable across the script. – Rajesh Chandrashekar Oct 12 '15 at 11:25
  • I have series of copy commands and based on the current time stamp which i store in the variable i need to collect data. so that all tables give data based on that variable. And i tried your suggestion and got the error: \i /home/cust/test3.sql psql:/home/cust/createFunction_3.sql:69: ERROR: syntax error at or near ":=" LINE 15: ...FROM table1 WHERE createdtime < _currenttime := NOW() -... Am new to adding post to stackoverflow, just getting to know things, forgive for the formatting. – Rajesh Chandrashekar Oct 12 '15 at 11:31

1 Answers1

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
$$;
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
  • I tried this, no error on executing function but there was no data copied to csv file, any idea ? But if i execute that command separately i get result – Rajesh Chandrashekar Oct 12 '15 at 12:00
  • **Ok using < _currenttime, get the following error**: select test3(); NOTICE: current time for archiving is 2015-10-11 18:01:17.461973 ERROR: column "_currenttime" does not exist LINE 1: ...py (SELECT * FROM table1 WHERE createdtime < _currentti... ^ QUERY: copy (SELECT * FROM table1 WHERE createdtime < _currenttime) TO '/tmp/table1.csv' CONTEXT: PL/pgSQL function test3() line 8 at SQL statement – Rajesh Chandrashekar Oct 12 '15 at 12:32
  • **Using '||_currenttime||'** select test3(); NOTICE: current time for archiving is 2015-10-11 17:59:51.982964 ERROR: invalid input syntax for type timestamp: "||_currenttime||" LINE 1: ...py (SELECT * FROM table1 WHERE createdtime < '||_curren... ^ QUERY: copy (SELECT * FROM table1 WHERE createdtime < '||_currenttime||') TO '/tmp/table1.csv' CONTEXT: PL/pgSQL function test3() line 8 at SQL statement – Rajesh Chandrashekar Oct 12 '15 at 12:34
  • The variable seems to be resolving but with some errors as below: select test3(); NOTICE: current time for archiving is 2015-10-11 19:10:37.398026 ERROR: syntax error at or near "19" LINE 1: ...* FROM table1 WHERE createdtime < 2015-10-11 19:10:37.3... ^ QUERY: copy (SELECT * FROM table1 WHERE createdtime < 2015-10-11 19:10:37.398026) TO '/tmp/table1.csv' CONTEXT: PL/pgSQL function test3() line 10 at EXECUTE statement – Rajesh Chandrashekar Oct 12 '15 at 13:43