PostgreSQL: pass ARRAY[] to pg_query_params

Question

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 ").

Community · Accepted Answer · 2017-05-23 12:19:20Z

5

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.

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered May 6, 2012 at 18:00

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

faintsignal Over a year ago

pg_escape_literal may come in handy here.

mu is too short Over a year ago

Wouldn't you be better off using the array constructor syntax if you have to do this by hand? Fewer levels of quoting to worry about at least.

Erwin Brandstetter Over a year ago

@muistooshort: An array constructor is functionality executed at the server side. Since the above is for a prepared statement, passed parameters need to be values, hence the array literal. Didn't test now, and barely remember this old answer, but I think, that's the reason.

mathieu · Accepted Answer · 2019-08-23 13:23:17Z

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;
}

Collectives™ on Stack Overflow

PostgreSQL: pass ARRAY[] to pg_query_params

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related