Re: string = any()

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Filip Rembialkowski
Sent: Tuesday, January 10, 2012 4:27 PM
To: Andy Colson
Cc: PostgreSQL
Subject: Re: [GENERAL] string = any()

no, I meant array constructor with reserved word ARRAY:

prepare x as select 'bob' = any( ARRAY[$2,$3] );
execute x( 'joe', 'bob' );

and in PHP:
<?php
$names = array( "joe", "bob" );
$placeholders = implode( ",", array_map( function($x){return "?"},
$names ) ); #I love Perl
$sql = "select 'bob' = any( ARRAY[$placeholders] )";
$stmt = $dbh->prepare($sql);
$stmt->execute($names);
?>

-----------------------------------------------------------------------------
Filip,

If you are going to supply one parameter per "possible value" anyway skip the whole "ANY" and "ARRAY" and just say " 'bob' IN ($1, $2 [,...]) "; The whole point of the exercise is to avoid dynamic SQL on the language side by ALWAYS having a single input regardless of how many possible values exists. Now, ideally you could pass in an actual ARRAY object from your programming language but as that, for whatever reason, tends to be clumsy or difficult the next best option is to pass in a single delimited string and then let PostGRESql convert it into an ARRAY and then use "= ANY($1)".

David J.