Incorrect SQL generated for pg_copy

Incorrect SQL generated for pg_copy_to()

Bug #73498	Incorrect SQL generated for pg_copy_to()
Submitted:	2016-11-11 13:44 UTC	Modified:	2016-11-21 11:45 UTC
From:	cward at titanhq dot com	Assigned:
Status:	Closed	Package:	PostgreSQL related
PHP Version:	5.6.28	OS:	FreeBSD
Private report:	No	CVE-ID:	None

[2016-11-11 13:44 UTC] 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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports

[2016-11-20 20:54 UTC] 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-21 11:45 UTC] 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-21 23:48 UTC] ab@php.net

-Status: Open +Status: Closed