Bug #73498 [Opn->Csd]: Incorrect SQL generated for pg_copy_to()

Edit report at https://bugs.php.net/bug.php?id=73498&edit=1 ID: 73498 Updated by: ab@php.net Reported by: cward at titanhq dot com Summary: Incorrect SQL generated for pg_copy_to() -Status: Open +Status: Closed Type: Bug Package: PostgreSQL related Operating System: FreeBSD PHP Version: 5.6.28 Block user comment: N Private report: N New Comment: Automatic comment on behalf of git@duncanc.co.uk Revision: http://git.php.net/?p=php-src.git;a=commit;h=644e290fcdc228d1990d8010c807dc39af6de4d5 Log: Fix bug #73498 Previous Comments: ------------------------------------------------------------------------ [2016-11-21 11:45:36] cward at titanhq dot com The 'DELIMITERS' syntax is still supported, but that syntax is from a much older version of Postgresql (7.2), which was limited to using COPY TO on a table only: https://www.postgresql.org/docs/current/static/sql-copy.html#AEN77789 The newer syntax would allow the use of a query or a table name as the data source. However, it requires using 'DELIMITER', and also moving the 'WITH' to earlier in the expression (or removing it entirely). I have manually tested the queries generated by pg_copy_to. This query form will not work in any version of postgres: COPY (select * from test) TO STDOUT DELIMITERS E' ' WITH NULL AS E'\\N' However, this one would work on any postgres version from 8.2 onwards, with a query or a table: COPY (select * from test) TO STDOUT WITH DELIMITER E' ' NULL AS E'\\N' COPY test TO STDOUT WITH DELIMITER E' ' NULL AS E'\\N' ------------------------------------------------------------------------ [2016-11-20 20:54:52] php at duncanc dot co dot uk The postgres docs also say that DELIMITERS is still supported, could the error message be misleading and the actual error be caused by some of the surrounding syntax? ------------------------------------------------------------------------ [2016-11-11 13:44:04] cward at titanhq dot com Description: ------------ pg_copy_to generates incorrect SQL for the Postgresql COPY TO statement. For instance: pg_copy_to($connection, 'test'); generates: COPY test TO STDOUT DELIMITERS E' ' WITH NULL AS E'\\N' "DELIMITERS" is incorrect; it should be "DELIMITER". See Postgresql manual: https://www.postgresql.org/docs/current/static/sql-copy.html#AEN77298 However, this defect is only apparent if you try to use pg_copy_to on a view, which is perfectly legal as documented here: https://www.postgresql.org/docs/current/static/sql-copy.html#AEN77520 Test script: --------------- pg_copy_to($connection, '(select * from test')); Expected result: ---------------- Return the contents of the view into a formatted array Actual result: -------------- ERROR: syntax error at or near "DELIMITERS" at character 129 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=73498&edit=1