PDO::pgsqlCopyToFile
(PHP 5 >= 5.3.3, PHP 7)
PDO::pgsqlCopyToFile — Copy data from table into file
Описание
public bool PDO::pgsqlCopyToFile
( string
$table_name
, string $filename
[, string $delimiter = '\t'
[, string $null_as = "\\\\N"
[, string $fields
]]] )
Copies data from table into file specified by filename
using delimiter as fields delimiter and fields list
Список параметров
-
table_name -
String containing table name
-
filename -
Filename to export data
-
delimiter -
Delimiter used in file specified by
filename -
null_as -
How to interpret null values
-
fields -
List of fields to insert
Возвращаемые значения
Returns TRUE on success, или FALSE в случае возникновения ошибки.
- PHP Руководство
- Функции по категориям
- Индекс функций
- Справочник функций
- Расширения для работы с базами данных
- Уровни абстракции
- Объекты данных PHP
- PDO Драйверы
- Функция PDO_PGSQL DSN() - Connecting to PostgreSQL databases
- PDO::pgsqlCopyFromArray
- PDO::pgsqlCopyFromFile
- PDO::pgsqlCopyToArray
- PDO::pgsqlCopyToFile
- PDO::pgsqlGetNotify
- PDO::pgsqlGetPid
- Функция PDO::pgsqlLOBCreate() - Creates a new large object
- Функция PDO::pgsqlLOBOpen() - Opens an existing large object stream
- Функция PDO::pgsqlLOBUnlink() - Deletes the large object
Коментарии
So I have been trying to use COPY (SELECT...) TO STDOUT WITH (FORMAT CSV, HEADER) but:
- PDO::query() uses prepare, which COPY does not support
- PDO::exec() does not return results
- PDO::pgsqlCopyToFile() does not support additional parameters, only delimiter and nulls (and columns)
But do you know what it does support? SQL Injections!
https://github.com/php/php-src/blob/7dfbf4d1b74b5e629f2331261944fa072a92e1cb/ext/pdo_pgsql/pgsql_driver.c#L870
spprintf(&query, 0, "COPY %s TO STDIN WITH DELIMITER E'%c' NULL AS E'%s'", ...
There is no escaping around any of that, so you can call this method like so:
<?php
$db->pgsqlCopyToFile("(select * from t1 cross join t2 using (whatever))", '/tmp/test.csv', ',', '\' header--');
?>
and that actually produces:
COPY (select...) TO STDIN WITH DELIMITER E',' NULL AS E'' header--
It still uses local filesystem, which I can live with, but at least I don't have to dance around the CSV headers.
If anyone knows of a better way to do this without copying the (in my case gigantic) resultset into array and fputcsv(), I'm all ears. And I've tried every combination with pg_query and pg_copy_to to no avail.