Обсуждение: array in function
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; BEGINEXECUTE '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/
Hello
pls, tryEXECUTE 'SELECT 1 FROM types WHERE type_id ANY($1) ' INTO hasValue USING _list;
Pavel
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
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/
Hello
2014-02-24 10:09 GMT+01:00 Pena Kupen <kupen@wippies.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
Pavel
You are right! This must be always taking case of. I have made this sample so simple as possible.p.s. newer try to merge variables to SQL string without sanitization - your
code is SQL injection vulnerable - and doesn't work
-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
Hello Pavel, I have taking little too much away from original sql :-) Now it works excellently! Thank's for your help! -kupen Pavel Stehule [pavel.stehule@gmail.com] kirjoitti: > Hello > > > 2014-02-24 10:09 GMT+01:00 Pena Kupen <kupen@wippies.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.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 > > > -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/