Thread: Escape parentheses in aggregate function

I want to copy query results to a text file and there's an aggregate
function in the SELECT expression. One of the aggregate function's
parentheses seems to end the \copy() function and I don't know how best to
write the statement. A minimal example:

\copy(select count_value, sum(count_value)
from table_name) to 'output_file.txt';

Do I write sumE'('count_valueE')', sum\(count_value\), or something else?

TIA,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:
> I want to copy query results to a text file and there's an aggregate
> function in the SELECT expression. One of the aggregate function's
> parentheses seems to end the \copy() function and I don't know how best to
> write the statement. A minimal example:

> \copy(select count_value, sum(count_value)
> from table_name) to 'output_file.txt';

I think your problem is the line break, not the parentheses.
psql knows how to count parens, but it has no concept of letting
backslash commands continue across lines.

            regards, tom lane

On Fri, 30 Aug 2019, Tom Lane wrote:

> I think your problem is the line break, not the parentheses. psql knows
> how to count parens, but it has no concept of letting backslash commands
> continue across lines.

Tom,

Interesting. I've adopted separating select statements by their expressions
as it makes it easier for me to get it right, or fix it. However, I can put
it all on a single line once I know the syntax is correct and I add the
\copy() function.

Thanks,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:
> On Fri, 30 Aug 2019, Tom Lane wrote:
>> I think your problem is the line break, not the parentheses. psql knows
>> how to count parens, but it has no concept of letting backslash commands
>> continue across lines.

> Interesting. I've adopted separating select statements by their expressions
> as it makes it easier for me to get it right, or fix it. However, I can put
> it all on a single line once I know the syntax is correct and I add the
> \copy() function.

FWIW, if you're running a current release then there's a reasonable
alternative for writing multi-line COPY-from-query commands; see commit
log below.

            regards, tom lane


Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL_12_BR [6d3ede5f1] 2019-01-26 14:15:42 -0500
Branch: REL_11_STABLE Release: REL_11_2 [2c50c9f23] 2019-01-26 14:15:42 -0500
Branch: REL_10_STABLE Release: REL_10_7 [8e97a97b3] 2019-01-26 14:15:42 -0500
Branch: REL9_6_STABLE Release: REL9_6_12 [ae4c7d5ab] 2019-01-26 14:15:42 -0500
Branch: REL9_5_STABLE Release: REL9_5_16 [cda1e27fb] 2019-01-26 14:15:42 -0500

    Fix psql's "\g target" meta-command to work with COPY TO STDOUT.

    Previously, \g would successfully execute the COPY command, but
    the target specification if any was ignored, so that the data was
    always dumped to the regular query output target.  This seems like
    a clear bug, so let's not just fix it but back-patch it.

    While at it, adjust the documentation for \copy to recommend
    "COPY ... TO STDOUT \g foo" as a plausible alternative.

    Back-patch to 9.5.  The problem exists much further back, but the
    code associated with \g was refactored enough in 9.5 that we'd
    need a significantly different patch for 9.4, and it doesn't
    seem worth the trouble.

    Daniel Vérité, reviewed by Fabien Coelho

    Discussion: https://postgr.es/m/15dadc39-e050-4d46-956b-dcc4ed098753@manitou-mail.org

On Fri, 30 Aug 2019, Tom Lane wrote:

> FWIW, if you're running a current release then there's a reasonable
> alternative for writing multi-line COPY-from-query commands; see commit
> log below.

Thanks, Tom. I'm running 11.1 and plan to upgrade to 11.5 this weekend.

Regards,

Rich