Re: string = any()

Hi all.

I am writing PHP where it prepares a statement like:
$sql = 'select * from aTable where id = any($1)';

then in php I create a string:
$args = "{1,2,3}";

And run it:

$q = pg_query_params($db, $sql, $args);

This is not actual code, just a sample. And it works great for
integers. I cannot get it to work with strings.

Just running this in psql does not work either:
select 'bob' = any( '{''joe'', ''bob'' }' )

But this does:
select 'bob' = any( array['joe', 'bob'] )

But I can't seem to prepare and execute:
$sql = "select 'bob' = any( $1 )";
$args = "array['joe', 'bob']";
$q = pg_query_params($db, $sql, $args);

Running on 9.0.4 on Slackware 64.

Any hits would be appreciated.

-Andy

maybe try to use ARRAY constructor instead?
http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

2012/1/10 Andy Colson <andy(at)squeakycode(dot)net>:
> Hi all.
>
> I am writing PHP where it prepares a statement like:
> $sql = 'select * from aTable where id = any($1)';
>
> then in php I create a string:
> $args = "{1,2,3}";
>
> And run it:
>
> $q = pg_query_params($db, $sql, $args);
>
> This is not actual code, just a sample.  And it works great for integers.  I
> cannot get it to work with strings.
>
> Just running this in psql does not work either:
> select 'bob' = any( '{''joe'', ''bob'' }' )
>
> But this does:
> select 'bob' = any( array['joe', 'bob'] )
>
> But I can't seem to prepare and execute:
> $sql = "select 'bob' = any( $1 )";
> $args = "array['joe', 'bob']";
> $q = pg_query_params($db, $sql, $args);
>
> Running on 9.0.4 on Slackware 64.
>
> Any hits would be appreciated.
>
> -Andy
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

>
> 2012/1/10 Andy Colson<andy(at)squeakycode(dot)net>:
>> Hi all.
>>
>> I am writing PHP where it prepares a statement like:
>> $sql = 'select * from aTable where id = any($1)';
>>
>> then in php I create a string:
>> $args = "{1,2,3}";
>>
>> And run it:
>>
>> $q = pg_query_params($db, $sql, $args);
>>
>> This is not actual code, just a sample. And it works great for integers. I
>> cannot get it to work with strings.
>>
>> Just running this in psql does not work either:
>> select 'bob' = any( '{''joe'', ''bob'' }' )
>>
>> But this does:
>> select 'bob' = any( array['joe', 'bob'] )
>>
>> But I can't seem to prepare and execute:
>> $sql = "select 'bob' = any( $1 )";
>> $args = "array['joe', 'bob']";
>> $q = pg_query_params($db, $sql, $args);
>>
>> Running on 9.0.4 on Slackware 64.
>>
>> Any hits would be appreciated.
>>
>> -Andy
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>

On 1/10/2012 9:11 AM, Filip Rembiałkowski wrote:
> maybe try to use ARRAY constructor instead?
>
http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
>
>

As I mentioned, I cannot get it to work:

clayia=# prepare x as select 'bob' = any($1);
PREPARE
Time: 0.665 ms
clayia=# execute x( 'array[''joe'', ''bob'']' );
ERROR: array value must start with "{" or dimension information
LINE 1: execute x( 'array[''joe'', ''bob'']' );

-Andy

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
Sent: Tuesday, January 10, 2012 10:04 AM
To: PostgreSQL
Subject: [GENERAL] string = any()

Hi all.

I am writing PHP where it prepares a statement like:
$sql = 'select * from aTable where id = any($1)';

then in php I create a string:
$args = "{1,2,3}";

And run it:

$q = pg_query_params($db, $sql, $args);

This is not actual code, just a sample. And it works great for integers. I
cannot get it to work with strings.

Just running this in psql does not work either:
select 'bob' = any( '{''joe'', ''bob'' }' )

But this does:
select 'bob' = any( array['joe', 'bob'] )

But I can't seem to prepare and execute:
$sql = "select 'bob' = any( $1 )";
$args = "array['joe', 'bob']";
$q = pg_query_params($db, $sql, $args);

Running on 9.0.4 on Slackware 64.

Any hits would be appreciated.

-Andy

------------------------------------------------------------------------

Explicit casting is required otherwise the system simply treats you input as
a simple scalar varchar.

" SELECT 'bob' = ANY( $1::varchar[] ) ... "

You can also pass in a delimited string and perform a "split_to_array($1,
',')" - didn't check exact syntax but you get the idea

David J.

On 1/10/2012 9:17 AM, David Johnston wrote:
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
> Sent: Tuesday, January 10, 2012 10:04 AM
> To: PostgreSQL
> Subject: [GENERAL] string = any()
>
> Hi all.
>
> I am writing PHP where it prepares a statement like:
> $sql = 'select * from aTable where id = any($1)';
>
> then in php I create a string:
> $args = "{1,2,3}";
>
> And run it:
>
> $q = pg_query_params($db, $sql, $args);
>
> This is not actual code, just a sample. And it works great for integers. I
> cannot get it to work with strings.
>
> Just running this in psql does not work either:
> select 'bob' = any( '{''joe'', ''bob'' }' )
>
> But this does:
> select 'bob' = any( array['joe', 'bob'] )
>
> But I can't seem to prepare and execute:
> $sql = "select 'bob' = any( $1 )";
> $args = "array['joe', 'bob']";
> $q = pg_query_params($db, $sql, $args);
>
> Running on 9.0.4 on Slackware 64.
>
> Any hits would be appreciated.
>
> -Andy
>
> ------------------------------------------------------------------------
>
> Explicit casting is required otherwise the system simply treats you input as
> a simple scalar varchar.
>
> " SELECT 'bob' = ANY( $1::varchar[] ) ... "
>
> You can also pass in a delimited string and perform a "split_to_array($1,
> ',')" - didn't check exact syntax but you get the idea
>
> David J.
>
>
>
>
>

Well, so close.

This still does not work, even in psql:
select 'bob' = any( '{''joe'', ''bob'' }'::varchar[] )

I cannot get a prepared version, or a php version to work either.

But this works in psql!
select 'bob' = any( string_to_array('joe,bob', ',') )

But not in php :-(

I still get errors:
Query failed: ERROR: array value must start with "{" or dimension
information

Its the same as if I try to prepare it in psql:
clayia=# prepare x as select 'bob' = any($1::varchar[]);
PREPARE
Time: 1.884 ms
clayia=# execute x( 'string_to_array(''joe,bob'', '','')' );
ERROR: array value must start with "{" or dimension information
LINE 1: execute x( 'string_to_array(''joe,bob'', '','')' );

-Andy

$$ My comments embedded below

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
Sent: Tuesday, January 10, 2012 10:33 AM
To: David Johnston
Cc: 'PostgreSQL'
Subject: Re: [GENERAL] string = any()

On 1/10/2012 9:17 AM, David Johnston wrote:
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
> Sent: Tuesday, January 10, 2012 10:04 AM
> To: PostgreSQL
> Subject: [GENERAL] string = any()
>
> Hi all.
>
> I am writing PHP where it prepares a statement like:
> $sql = 'select * from aTable where id = any($1)';
>
> then in php I create a string:
> $args = "{1,2,3}";
>
> And run it:
>
> $q = pg_query_params($db, $sql, $args);
>
> This is not actual code, just a sample. And it works great for
> integers. I cannot get it to work with strings.
>
> Just running this in psql does not work either:
> select 'bob' = any( '{''joe'', ''bob'' }' )
>
> But this does:
> select 'bob' = any( array['joe', 'bob'] )
>
> But I can't seem to prepare and execute:
> $sql = "select 'bob' = any( $1 )";
> $args = "array['joe', 'bob']";
> $q = pg_query_params($db, $sql, $args);
>
> Running on 9.0.4 on Slackware 64.
>
> Any hits would be appreciated.
>
> -Andy
>
> ----------------------------------------------------------------------
> --
>
> Explicit casting is required otherwise the system simply treats you
> input as a simple scalar varchar.
>
> " SELECT 'bob' = ANY( $1::varchar[] ) ... "
>
> You can also pass in a delimited string and perform a
> "split_to_array($1, ',')" - didn't check exact syntax but you get the
> idea
>
> David J.
>
>
>
>
>

Well, so close.

This still does not work, even in psql:
select 'bob' = any( '{''joe'', ''bob'' }'::varchar[] )

$$ ^ This works for me just fine....though I am not using psql; are
you having quoting issues? What error do you get?

I cannot get a prepared version, or a php version to work either.

But this works in psql!
select 'bob' = any( string_to_array('joe,bob', ',') )

But not in php :-(

I still get errors:
Query failed: ERROR: array value must start with "{" or dimension
information

Its the same as if I try to prepare it in psql:
clayia=# prepare x as select 'bob' = any($1::varchar[]); PREPARE
Time: 1.884 ms
clayia=# execute x( 'string_to_array(''joe,bob'', '','')' );

$$ ^ Why do you have single-quotes surrounding "string_to_array";
the EXECUTE now sees the entire literal 'string_to_array....' as a single
scalar value and thus does not resolve the function call into an array.

ERROR: array value must start with "{" or dimension information LINE 1:
execute x( 'string_to_array(''joe,bob'', '','')' );

$$ I use Java as my main language and PostgreSQL Maestro as my GUI.
Can you try working with pgAdmin3 instead of (or in addition to) psql and
see what results you get then.
$$ If you get it to work with psql/pgAdmin you should be able to do
the same with php by keeping in mind you want to be passing literals and let
PostgreSQL take care of parsing it into an array (via casting or
string_to_array()).

Andy Colson <andy(at)squeakycode(dot)net> writes:
> This still does not work, even in psql:
> select 'bob' = any( '{''joe'', ''bob'' }'::varchar[] )

You've got the array quoting rules wrong here (should be double quote
marks, not single quotes). You didn't show us your PHP code but I
surmise that's got the same mistake. Read the array I/O representation
spec carefully:
http://www.postgresql.org/docs/9.1/static/arrays.html#ARRAYS-IO

regards, tom lane

On 1/10/2012 10:11 AM, Tom Lane wrote:
> Andy Colson<andy(at)squeakycode(dot)net> writes:
>> This still does not work, even in psql:
>> select 'bob' = any( '{''joe'', ''bob'' }'::varchar[] )
>
> You've got the array quoting rules wrong here (should be double quote
> marks, not single quotes). You didn't show us your PHP code but I
> surmise that's got the same mistake. Read the array I/O representation
> spec carefully:
> http://www.postgresql.org/docs/9.1/static/arrays.html#ARRAYS-IO
>
> regards, tom lane

Wahoo! Yep, that was it.

My confusion was from:

clayia=# select '{''joe'', ''bob'' }';
?column?
-----------------
{'joe', 'bob' }
(1 row)

It does return an array of string, or so it looks.

Thank you Tom.

-Andy

> This still does not work, even in psql:
> select 'bob' = any( '{''joe'', ''bob'' }'::varchar[] )
>
> $$ ^ This works for me just fine....though I am not using psql; are
> you having quoting issues? What error do you get?
>

It runs, but it returns false. I get false at least. I'm assuming you
do to, otherwise something weird is going on.

> Its the same as if I try to prepare it in psql:
> clayia=# prepare x as select 'bob' = any($1::varchar[]); PREPARE
> Time: 1.884 ms
> clayia=# execute x( 'string_to_array(''joe,bob'', '','')' );
>
> $$ ^ Why do you have single-quotes surrounding "string_to_array";
> the EXECUTE now sees the entire literal 'string_to_array....' as a single
> scalar value and thus does not resolve the function call into an array.

Because it simulates how php is actually running the function. I
prepare the statement, and pass it a string.

execute x( 'string_to_array(''joe,bob'', '','')' );

That simulates pass a single string. It also results in the same
error's that php is getting... so I just assumed I was testing it correct.

Without the string works fine... but that's not how php works.
execute x( string_to_array('joe,bob', ',') );

You should have the same problem in java, if you:
q = databaseFactory.queryFactory.prepare("select 'bob' =
any($1::varchar[])");

:-) just kidding

Then when you execute it, you have to pass the argument as a string:
q.execute("{'joe','bob'}");

yeah, I'm thinking the execute param step is not running the param
through the entire parse/analyze/whatnot step, so I cannot use functions.

Eh, well, the input is from a website, so wanted to use prepared queries
as much as possible, but I'll just pg_escape all the strings and cat
them all into the sql statement itself.

Ok, nevermind, just got Tom's email. Double Quotes...

Thanks for the help,

-Andy

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
Sent: Tuesday, January 10, 2012 11:20 AM
To: David Johnston
Cc: 'PostgreSQL'
Subject: Re: [GENERAL] string = any()

> This still does not work, even in psql:
> select 'bob' = any( '{''joe'', ''bob'' }'::varchar[] )
>
> $$ ^ This works for me just fine....though I am not using psql; are
> you having quoting issues? What error do you get?
>

It runs, but it returns false. I get false at least. I'm assuming you do
to, otherwise something weird is going on.

-----------------------------------------

I execute the following:

SELECT 'bob' = ANY( '{ "joe", "bob" }'::varchar[] );

And it returns TRUE...

This returns FALSE:

SELECT 'bob' = ANY( '{ "joe", "dave" }'::varchar[] );

I didn't enable logging so I don't know exactly what the engine is seeing
but using PostgreSQL Maestro that is what I am getting; and from the
documentation it seems correct...

I am using the "string_to_array()" function call where I do this kind of
thing because I probably encountered the same Java API issue that you are
with PHP; but since passing in the delimited string and splitting it isn't
that difficult I am not all that concerned. You need to embed the
"string_to_array" inside the prepared statement and pass only scalars via
the API.

So:

prepare x as select 'bob' = any(string_to_array($1, ',')::varchar[]);
execute x ('joe,bob,billy');

David J.

On 1/10/2012 10:28 AM, David Johnston wrote:
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
> Sent: Tuesday, January 10, 2012 11:20 AM
> To: David Johnston
> Cc: 'PostgreSQL'
> Subject: Re: [GENERAL] string = any()
>
>> This still does not work, even in psql:
>> select 'bob' = any( '{''joe'', ''bob'' }'::varchar[] )
>>
>> $$ ^ This works for me just fine....though I am not using psql; are
>> you having quoting issues? What error do you get?
>>
>
> It runs, but it returns false. I get false at least. I'm assuming you do
> to, otherwise something weird is going on.
>
> -----------------------------------------
>
> I execute the following:
>
> SELECT 'bob' = ANY( '{ "joe", "bob" }'::varchar[] );
>
> And it returns TRUE...
>
> This returns FALSE:
>
> SELECT 'bob' = ANY( '{ "joe", "dave" }'::varchar[] );

Yeah, that uses the correct double quotes... which I was not using.

>
> So:
>
> prepare x as select 'bob' = any(string_to_array($1, ',')::varchar[]);
> execute x ('joe,bob,billy');
>
> David J.
>

Ah, that's a good idea, I hadn't thought of that. Always another way to
skin a cat.

Thanks again,

-Andy

Andy Colson <andy(at)squeakycode(dot)net> writes:
> My confusion was from:

> clayia=# select '{''joe'', ''bob'' }';
> ?column?
> -----------------
> {'joe', 'bob' }
> (1 row)

> It does return an array of string, or so it looks.

Yeah, it's not obvious that those quote marks are really data
characters. I wonder whether we could get away with tweaking array_out
to consider single-quote marks as being grounds for quoting an array
element. Then you would have seen a result like

?column?
---------------------
{"'joe'", "'bob'"}

which might at least have given you an inkling of what was happening.

regards, tom lane

On 01/10/12 8:18 AM, Andy Colson wrote:
>
> clayia=# select '{''joe'', ''bob'' }';
> ?column?
> -----------------
> {'joe', 'bob' }
> (1 row)
>
> It does return an array of string, or so it looks.

I'm pretty sure that's just a string, not an array.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

W dniu 10 stycznia 2012 16:17 użytkownik Andy Colson
<andy(at)squeakycode(dot)net> napisał:
>> 2012/1/10 Andy Colson<andy(at)squeakycode(dot)net>:
>>> I am writing PHP where it prepares a statement like:
>>> $sql = 'select * from aTable where id = any($1)';
>>>
>>> then in php I create a string:
>>> $args = "{1,2,3}";
>>>
>>> And run it:
>>>
>>> $q = pg_query_params($db, $sql, $args);

> On 1/10/2012 9:11 AM, Filip Rembiałkowski wrote:
>> maybe try to use ARRAY constructor instead?
>>
>> http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

>
> As I mentioned, I cannot get it to work:
>
> clayia=# prepare x as select 'bob' = any($1);
> PREPARE
> Time: 0.665 ms
> clayia=# execute x( 'array[''joe'', ''bob'']' );
> ERROR:  array value must start with "{" or dimension information
> LINE 1: execute x( 'array[''joe'', ''bob'']' );
>

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);
?>

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

W dniu 10 stycznia 2012 22:52 użytkownik David Johnston
<polobo(at)yahoo(dot)com> napisał:

> 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 [,...]) ";

true :-)

<?php
$names = array( "joe", "bob" );
$placeholders = implode( ",", array_map( function($x){return "?"}, $names ) );
$sql = "select 'bob' IN ($placeholders)";
$stmt = $dbh->prepare($sql);
$stmt->execute($names);
?>

PHP from several years cries for postgres array support in PDO. And
personally I would rather discourage programmers from constructing
delimited array string in code - more complicated than first option,
needs careful character escaping, etc.

select array['a',null,'','tab is ','quote is "','comma is ,'];
array
-------------------------------------------------------
{a,NULL,"","tab is ","quote is \"","comma is ,"}