Re: string = any()

$$ My comments embedded below

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.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()).