Re: array in function

Hi,

I try to change it:

ERROR:  syntax error at or near "ANY" at character 35
QUERY:  SELECT 1 FROM types WHERE type_id ANY($1) CONTEXT:  PL/pgSQL function "hastype" line 4 at EXECUTE statement

p.s. newer try to merge variables to SQL string without sanitization - your
code is SQL injection vulnerable - and doesn't work

You are right! This must be always taking case of. I have made this sample so simple as possible.
-kupen

Pavel Stehule [pavel.stehule@gmail.com] kirjoitti:

Hello

pls, try

EXECUTE 'SELECT 1 FROM types WHERE type_id ANY($1) ' INTO hasValue USING
_list;


Regards

Pavel

p.s. newer try to merge variables to SQL string without sanitization - your
code is SQL injection vulnerable - and doesn't work


2014-02-24 9:42 GMT+01:00 Pena Kupen <kupen@wippies.fi>:

> Hi,
>
> I have a problem with function, where I want to use execute and create sql
> for it.
>
> My table is:
> create table types (
> id integer,
> type_id character varying,
> explain character varying
> );
>
> And function:
> CREATE or REPLACE FUNCTION hasType(_list character varying[]) RETURNS
> integer
>    LANGUAGE plpgsql
>    AS $$
>
> DECLARE hasValue integer;
> BEGIN
>         EXECUTE 'SELECT 1 FROM types WHERE type_id ANY('|| _list ||') '
> INTO hasValue;
>         IF hasValue IS NULL THEN
>                 RETURN 0;
>         ELSE
>                 RETURN 1;
>         END IF;
> END;
> $$;
>
> Executing function with array parameter:
> select hasType(ARRAY['E','F','','']);
>
> I got error:
> SQL error:
> ERROR:  operator is not unique: unknown || character varying[] at
> character 49
> HINT:  Could not choose a best candidate operator. You might need to add
> explicit type casts.
> QUERY:  SELECT  'SELECT 1 FROM types WHERE type_id ANY('||  $1  ||') '
> CONTEXT:  PL/pgSQL function "hastype" line 4 at EXECUTE statement
> In statement:
> select hasType(ARRAY['E','F','','']);
>
> How to add array in parameter list to sql-sentence?
>
> -kupen
>
>
> --
> Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen
> eturintamassa ja liity Wippiesiin heti!
> http://www.wippies.com/
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

--
Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti!
http://www.wippies.com/




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql