Re: 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[] )

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