From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | 'Filip Rembiałkowski' <plk(dot)zuber(at)gmail(dot)com>, "'Andy Colson'" <andy(at)squeakycode(dot)net> |
Cc: | "'PostgreSQL'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: string = any() |
Date: | 2012-01-10 21:52:16 |
Message-ID: | 01ff01cccfe2$20fc83c0$62f58b40$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----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.
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-01-10 22:05:32 | Re: Efficient map looking table |
Previous Message | Yoann MOREAU | 2012-01-10 21:44:32 | Efficient map looking table |