CODEWITHSUNDEEP
phparrayspostgresql
Dmitry
Dmitry

Reputation: 7553

PostgreSQL: pass ARRAY[] to pg_query_params

I need to do this query: 

SELECT * FROM property_select(ARRAY[8,9,10,11,12,13,14,15,16,17,19,20,26,28])

using PHP function pg_query_params($prepared, $params).

Prepared query is:

SELECT * FROM property_select($1);

Params are: ["ARRAY[8,9,10,11,12,13,14,15,16,17,19,20,26,28]"] How can I pass the params to pg_query_params() as array?

It's not possible to use '{8,9,10,11,12,13,14,15,16,17,19,20,26,28}' postgres arrays (because it may contain strings and strings inside this array may contain , and ").

Upvotes: 4

Views: 5304

Answers (2)

mathieu
mathieu

Reputation: 477

Here is a tricky but efficient and safe way of doing this.

$link: postgresql connection resource

$sql: a query, for example: SELECT * FROM user WHERE id = ANY(@ids)

$params: an associative array: $params = array("ids" => array(1,2,3,6));

function pg_query_params_assoc($link, $sql, $params)
{   
    $index = 0;
    $assoc = [];
    $hash = [];
    foreach($params as $k => $v)
    {
        if(array_key_exists($k, $hash)) continue;
        if(false === strpos($sql, "@".$k)) continue;
        $hash[$k] = true;

        if(is_array($v))
        {
            $keys = [];
            foreach($v as $item)
            {
                $keys[] = $key = '$'.(++$index);
                $assoc[$key] = $item;
            }           
            $sql = str_replace("@".$k, "array[".implode(',', $keys)."]", $sql);
        }
        else
        {
            $assoc[$key = '$'.(++$index)] = $v;
            $sql = str_replace("@".$k, $key, $sql);         
        }       
    }

    $res = pg_query_params($link, $sql, $assoc);
    return $res;
}

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658717

Find the detailed requirements for PostgreSQL Array Input and Output Syntax in the manual.

Basically, you need to enclose array elements with special characters in double quotes "". You can double-quote all elements, but you don't have to. And, I quote the manual (see above):

To put a double quote or backslash in a quoted array element value, use escape string syntax and precede it with a backslash.

There is a piece of PHP code posted by a user in the manual:

  //$t is array to be escaped. $u will be string literal.
  $tv=array();
  foreach($t as $key=>$val){
    $tv[$key]="\"" .
      str_replace("\"",'\\"', str_replace('\\','\\\\',$val)) . "\"
";
  }
  $u= implode(",",$tv) ;
  $u="'{" . pg_escape_string($u) . "}'";

There is also a related answer here on SO:

Also consider the setting of standard_conforming_strings. Backslashes may need to be doubled, but PHP's pg-modules should do that for you automatically.

Upvotes: 5

Related Questions