Search Postgresql Archives

variable in COPY <table> TO variable

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Dear postgres-users,

I'm currently working on a stored procedure, but having an error which
seems odd.

I'm following the synatx

COPY <tablename> TO 'filename'

.....but i'm trying to use a variable as the filename.

1 : CREATE OR REPLACE FUNCTION archive() RETURNS void AS
2 : $BODY$DECLARE
3 : 	ts timestamp without time zone;
4 : 	v_year integer;
5 : 	v_month integer;
6 : 	filename varchar(100);
7 : BEGIN
8 : 	FOR i in 2..12 LOOP
9 : 		ts = now() - (i || ' months')::interval;
10: 		v_year = EXTRACT(YEAR FROM ts);
11: 		v_month = EXTRACT(MONTH FROM ts);
12:
13: 		IF (SELECT COUNT(1) FROM call_tickets WHERE EXTRACT(YEAR FROM
"Date/Hour") = v_year AND EXTRACT(MONTH FROM "Date/Hour") = v_month) >
0 THEN
14: 			DROP TABLE IF EXISTS tt_archive;
15: 			CREATE TEMP TABLE tt_archive AS (SELECT * FROM call_tickets
WHERE EXTRACT(YEAR FROM "Date/Hour") = v_year AND EXTRACT(MONTH FROM
"Date/Hour") = v_month);
16: 			filename = '/usr/local/var/telephony_data/archive/
telephony_archive-' || v_year || v_month || '.psql';
17: 			COPY tt_archive TO filename;
18: 		END IF;
19: 	END LOOP;
20: END; $BODY$
21: LANGUAGE 'plpgsql' VOLATILE;

returns the error....

ERROR: syntax error at or near "$1" at character 21
QUERY: COPY tt_archive TO $1
CONTEXT:  SQL statement in PL/PgSQL function "archive" near line 16


any pointer greatly appreciated.

R


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux