Use postgres constants with pg_prepare/pg

Supposing this table:

CREATE TABLE log
(
  user smallint NOT NULL,
  moment timestamp without time zone NOT NULL DEFAULT (clock_timestamp())::timestamp without time zone,
  details text NOT NULL,
  CONSTRAINT log_pk PRIMARY KEY (user, moment)
);

To add a record to the table, I would use the following code:

<?php

  $dbconn = pg_connect('dbname=system');

  $query = <<<EOQ
INSERT INTO
  log
VALUES
  (
    $1,
    DEFAULT,
    $3
  )
EOQ;

// ...

With the 'DEFAULT' constant as the second parameter, but for a "collection of bizarre reasons" I need to be able to run it like this:

<?php

  $dbconn = pg_connect('dbname=system');

  $query = <<<EOQ
INSERT INTO
  log
VALUES
  (
    $1,
    $2,
    $3
  )
EOQ;

  $result = pg_prepare($dbconn, '', $query);

  $data=
    [
      10,
      'DEFAULT',
      'IP: 127.0.0.1'
    ];

  $result = pg_execute($dbconn, '', $data);

The problem is that, in this way, the constant is received as text and php raise the error 'PHP Warning: pg_execute(): Query failed: ERROR: invalid input syntax for type timestamp: "DEFAULT"'.

Is there any way to use postgres constants as parameters with pg_prepare/pg_execute?

PS: I'm using php 8 and postgres 13.4

EDIT:

I also tried using this format:


// ...

  $query = <<<EOQ
INSERT INTO
  acessos
VALUES
  (
    $1,
    CASE WHEN $2='DEFAULT' THEN
      DEFAULT
    ELSE
      $2
    END,
    $3
  )
EOQ;

// ...

but I got another error message, 'PHP Warning: pg_prepare(): Query failed: ERROR: DEFAULT is not allowed in this context'...

CodePudding user response：

DEFAULT is an SQL keyword, so part of the SQL statement, and you cannot use a parameter for it. You'll have to use two different INSERT statements in your PHP code, one that inserts moment, and one that doesn't.

The alternative is to use dynamic SQL, but I think that would be less readable and more complicated (and you have to be careful to avoid SQL injection).

CodePudding user response：

A "collection of bizarre reasons" calls for a bizarre solution. If you have to supply 3 parameters but only use 2 then I suggest to change the query so that it will silently ignore the second one.

$query = <<<EOQ
INSERT INTO log
VALUES
(
 case when $2 is null or $2 is not null then $1 end, -- const.true but $2 is 'used'
 DEFAULT,
 $3
)
EOQ;

or, better, use the default expression explicitly

$query = <<<EOQ
INSERT INTO
  acessos
VALUES
  (
    $1,
    CASE WHEN $2 ~* '^DEFAULT$' THEN clock_timestamp() ELSE $2::timestamp END,
    $3
  )
EOQ;