Postgres Pro
Downloads
Home   >   mailing lists

Thread: variable in COPY TO variable

variable in COPY

TO variable
From
Rob Shepherd
Date:
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

Re: variable in COPY

TO variable
From
"Albe Laurenz"
Date:
Rob Shepherd wrote:
> I'm following the synatx
>
> COPY <tablename> TO 'filename'
>
> .....but i'm trying to use a variable as the filename.
>
[...]
>
> 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

A case for dynamic SQL!
http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQ
L-STATEMENTS-EXECUTING-DYN

Yours,
Laurenz Albe

Re: variable in COPY

TO variable
From
Rob Shepherd
Date:
> A case for dynamic SQL!http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQ
> L-STATEMENTS-EXECUTING-DYN
>
> Yours,
> Laurenz Albe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Perfect. Thanks for the assistance Laurenz.

Very useful.

Rob