Re: array in function

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(at)wippies(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>