\copy Variable Substitution in 9.1.2

From: Gary Chambers <gwchamb(at)gwcmail(dot)com>
To: PostgreSQL General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: \copy Variable Substitution in 9.1.2
Date: 2012-03-21 17:59:54
Message-ID: alpine.DEB.2.00.1203211339350.25725@equinox.garychambers.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

All,

I'm trying to use a variable for the filename portion of \copy. I'm calling
psql on some SQL commands in a file from a shell script:

FILETOLOAD="/var/tmp/filetoload.${$}"
OPTIONS="--variable=outfile='${FILETOLOAD}'"
psql ${OPTIONS} -f /some/file.sql

In /some/file.sql:

\copy table(f1, f2, f3) from :outfile with delimiter '|';

Which yields:

psql:/some/file.sql:4: :outfile: No such file or directory

An \echo :outfile command confirms that the single-quoted filename is making
it to the SQL script, but it's not being used in the substitution by the
\copy command.

The only thing applicable to variable substitution with \copy I could find
was a somewhat contradictory paragraph in the psql man page:

The syntax of the command is similar to that of the SQL COPY(7) command.
Note that, because of this, special parsing rules apply to the \copy
command. In particular, the variable substitution rules and backslash
escapes do not apply.

Thanks for any suggestions.

--
Gary Chambers

Browse pgsql-general by date

  From Date Subject
Next Message Lee Hachadoorian 2012-03-21 18:14:57 Re: huge price database question..
Previous Message Steve Crawford 2012-03-21 17:27:11 Re: huge price database question..