Обсуждение: pg_query_params() problem with date_trunc() (possibly other functions as well)

Hi list,

My google skills are very poor, so excuse me if this is
documented some place I'm failing to find.

I started to look into using pg_query_params() with some
php scripts, but ran into this issue where I get:

PHP message: PHP Warning: pg_query_params(): Query failed: ERROR:
invalid input syntax for type timestamp: "$1"
LINE 1: ... date_trunc('day', now()), date_trunc('day', TIMESTAMP '$1')

(excuse typos if any -- I'm hand-typing from a different screen).

The test query is a simple

SELECT date_trunc('day', now()),
    date_trunc('day', TIMESTAMP '2014-05-16 21:00:00');

where the '2014-05-16 21:00:00' is an input parameter.


Trying with pg_query_params() fails as mentioned:

$s = "2014-05-16 21:00:00";
$res = pg_query_params($db,
    "SELECT date_trunc('day', now()), "
    ."date_trunc('day', TIMESTAMP '" . '$1' . "')",
    array($s));

If I remove the single quotes for TIMESTAMP the syntax
error message changes to:

Query failed: ERROR: syntax error at or near "$1"
LINE 1: ... date_trunc('day', now()), date_trunc('day', TIMESTAMP $1)


while the pg_query() method works as expected:

$sql = sprintf("SELECT date_trunc('day', now()), "
    ."date_trunc('day', TIMESTAMP '%s')",
    pg_escape_string($db, $s));
$res = pg_query($db, $sql);


Where is the bug? my code? my understanding of how
this is supposed to work? in pg_query_params()?

Best,
--patrick

patrick keshishian <pkeshish@gmail.com> writes:
> I started to look into using pg_query_params() with some
> php scripts, but ran into this issue where I get:

> PHP message: PHP Warning: pg_query_params(): Query failed: ERROR:
> invalid input syntax for type timestamp: "$1"
> LINE 1: ... date_trunc('day', now()), date_trunc('day', TIMESTAMP '$1')

You probably want just this:

... date_trunc('day', now()), date_trunc('day', $1)

or if that doesn't work, try this:

... date_trunc('day', now()), date_trunc('day', $1::timestamp)

The syntax TIMESTAMP '...' is only for plain literal constants.

            regards, tom lane

On 5/16/14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> patrick keshishian <pkeshish@gmail.com> writes:
>> I started to look into using pg_query_params() with some
>> php scripts, but ran into this issue where I get:
>
>> PHP message: PHP Warning: pg_query_params(): Query failed: ERROR:
>> invalid input syntax for type timestamp: "$1"
>> LINE 1: ... date_trunc('day', now()), date_trunc('day', TIMESTAMP '$1')
>
> You probably want just this:
>
> ... date_trunc('day', now()), date_trunc('day', $1::timestamp)

This worked.

Thank you for your quick response!
--patrick

>
> The syntax TIMESTAMP '...' is only for plain literal constants.
>
>             regards, tom lane
>