From: | Rob Shepherd <rgshepherd(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | variable in COPY <table> TO variable |
Date: | 2007-10-16 15:21:00 |
Message-ID: | 1192548060.395271.35130@v23g2000prn.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2007-10-16 16:02:22 | Preserving view source code |
Previous Message | Tom Lane | 2007-10-16 15:16:46 | Re: improvement proposition |