Re: psql \set vs \copy - bug or expected behaviour?

On Fri, Oct 21, 2011 at 05:31:41PM -0400, Robert Haas wrote:
> On Fri, Oct 21, 2011 at 7:24 AM, Richard Huxton <dev@archonet.com> wrote:
> > It looks like \copy is just passing the text of the query unadjusted to
> > "COPY". I get a syntax error on ":x" with the \copy below on both 9.0 and
> > 9.1
> >
> > === test script ===
> > \set x '''HELLO'''
> > -- Works
> > \echo :x
> > -- Works
> > \o '/tmp/test1.txt'
> > COPY (SELECT :x) TO STDOUT;
> > -- Doesn't work
> > \copy (SELECT :x) TO '/tmp/test2.txt'
> > === end script ===
>
> I'm not sure whether that's a bug per se, but I can see where a
> behavior change might be an improvement.

I did some research on this and learned a little more about flex rules.

Turns out we can allow variable substitution in psql whole-line
commands, like \copy and \!, by sharing the variable expansion flex
rules with the code that does argument processing.

What we can't easily do is to allow quotes to prevent variable
substitution in these whole-line commands because we can't process the
quotes because that will remove them.

Here are some examples;  \copy and \! behave the same:

    test=> \set x abc
    test=> \echo :x
    abc
    test=> \echo ":x"
-->    ":x"
    test=> \! echo :x
    abc
    test=> \! echo ":x"
-->    abc

Notice the last line has expanded :x even though it is in quotes.

So, what do we want?  The attached patch is pretty short.

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

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