Postgres Pro
Downloads
Home   >   mailing lists

Thread: psql's \copy incompatible with :variables

psql's \copy incompatible with :variables

From
Josh Berkus
Date:
Summary: \copy interprets psql's :variables as literal strings

Tested On: 8.4.4, 9.0.4

Severity: Annoyance

Steps to Reproduce:

    psql

    \set filename 'test.csv'

    \copy pg_class to :filename with csv

    \q

    ls

    :filename

Note that psql variables work perfectly fine with COPY.  It's just \copy
which seems to be misbehaving.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: psql's \copy incompatible with :variables

From
Bruce Momjian
Date:
Josh Berkus wrote:
> Summary: \copy interprets psql's :variables as literal strings
>
> Tested On: 8.4.4, 9.0.4
>
> Severity: Annoyance
>
> Steps to Reproduce:
>
>     psql
>
>     \set filename 'test.csv'
>
>     \copy pg_class to :filename with csv
>
>     \q
>
>     ls
>
>     :filename
>
> Note that psql variables work perfectly fine with COPY.  It's just \copy
> which seems to be misbehaving.

The problem is that none of the backslash commands interpret variables:

    test=> \set x '''y'''
    test=> select :x;
     ?column?
    ----------
     y
    (1 row)

    test=> \d :y
    Did not find any relation named ":y".

Should they?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: psql's \copy incompatible with :variables

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> Josh Berkus wrote:
>> Note that psql variables work perfectly fine with COPY.  It's just \copy
>> which seems to be misbehaving.

> The problem is that none of the backslash commands interpret variables:

Nonsense.

regression=# \set foo bar
regression=# \d foo
Did not find any relation named "foo".
regression=# \d :foo
Did not find any relation named "bar".

\copy is different because it uses OT_WHOLE_LINE mode to read the
argument, and that doesn't expand :variable references.  I'd be a bit
leery of changing that.

            regards, tom lane

Re: psql's \copy incompatible with :variables

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Josh Berkus wrote:
> >> Note that psql variables work perfectly fine with COPY.  It's just \copy
> >> which seems to be misbehaving.
>
> > The problem is that none of the backslash commands interpret variables:
>
> Nonsense.
>
> regression=# \set foo bar
> regression=# \d foo
> Did not find any relation named "foo".
> regression=# \d :foo
> Did not find any relation named "bar".
>
> \copy is different because it uses OT_WHOLE_LINE mode to read the
> argument, and that doesn't expand :variable references.  I'd be a bit
> leery of changing that.

Oh, I see.  My tests were wrong.  Thanks.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: psql's \copy incompatible with :variables

From
Josh Berkus
Date:
> \copy is different because it uses OT_WHOLE_LINE mode to read the
> argument, and that doesn't expand :variable references.  I'd be a bit
> leery of changing that.

So, doc warning then?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: psql's \copy incompatible with :variables

From
Bruce Momjian
Date:
Josh Berkus wrote:
>
> > \copy is different because it uses OT_WHOLE_LINE mode to read the
> > argument, and that doesn't expand :variable references.  I'd be a bit
> > leery of changing that.
>
> So, doc warning then?

I don't think enough people have hit this problem to warrant that.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +