Re: array in function

Hello

2014-02-24 10:09 GMT+01:00 Pena Kupen <kupen(at)wippies(dot)fi>:

> 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

predicate should be

type_id = ANY($1)

Regards

Pavel

>
>
> 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(dot)stehule(at)gmail(dot)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(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
>> >
>>
>>
>
> --
> 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
>