agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedarray in function
5+ messages / 2 participants
[nested] [flat]
* array in function
@ 2014-02-24 08:42 Pena Kupen <kupen@wippies.fi>
2014-02-24 08:55 ` Re: array in function Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pena Kupen @ 2014-02-24 08:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: array in function
2014-02-24 08:42 array in function Pena Kupen <kupen@wippies.fi>
@ 2014-02-24 08:55 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2014-02-24 08:55 UTC (permalink / raw)
To: Pena Kupen <kupen@wippies.fi>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: array in function
@ 2014-02-24 09:09 Pena Kupen <kupen@wippies.fi>
2014-02-24 09:31 ` Re: array in function Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pena Kupen @ 2014-02-24 09:09 UTC (permalink / raw)
To: 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/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: array in function
2014-02-24 09:09 Re: array in function Pena Kupen <kupen@wippies.fi>
@ 2014-02-24 09:31 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2014-02-24 09:31 UTC (permalink / raw)
To: Pena Kupen <kupen@wippies.fi>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: array in function
@ 2014-02-24 10:05 Pena Kupen <kupen@wippies.fi>
0 siblings, 0 replies; 5+ messages in thread
From: Pena Kupen @ 2014-02-24 10:05 UTC (permalink / raw)
To: 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/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-02-24 10:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-02-24 08:42 array in function Pena Kupen <kupen@wippies.fi>
2014-02-24 08:55 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-02-24 09:09 Re: array in function Pena Kupen <kupen@wippies.fi>
2014-02-24 09:31 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-02-24 10:05 Re: array in function Pena Kupen <kupen@wippies.fi>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox