pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelecting values from comma separated string
7+ messages / 4 participants
[nested] [flat]
* Selecting values from comma separated string
@ 2009-08-26 13:19 Nacef LABIDI <nacef.l@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Nacef LABIDI @ 2009-08-26 13:19 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I want to write a function that takes as param a comma separated values
string and perform a select matching these values.
Here is the string '1,3,7,8'
And I wan to perform a : SELECT * FROM my_table WHERE id IN (1, 3, 7, 8);
Does anyone have a clue ?
Thanks
Nacef
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Selecting values from comma separated string
@ 2009-08-26 13:30 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Nacef LABIDI <nacef.l@gmail.com>
1 sibling, 2 replies; 7+ messages in thread
From: A. Kretschmer @ 2009-08-26 13:30 UTC (permalink / raw)
To: pgsql-sql
In response to Nacef LABIDI :
> Hi all,
>
> I want to write a function that takes as param a comma separated values string
> and perform a select matching these values.
>
> Here is the string '1,3,7,8'
>
> And I wan to perform a : SELECT * FROM my_table WHERE id IN (1, 3, 7, 8);
Use EXECUTE sql_string, see
http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
For instance, simple example, untested:
create function foo (my_string) returns setof record as $$
declare
sql text;
begin
sql:='SELECT * FROM my_table WHERE id IN (' || $1 || ')';
return query execute sql;
end;
The variable sql contains the whole query, and then execute that.
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Selecting values from comma separated string
@ 2009-08-26 13:32 Pavel Stehule <pavel.stehule@gmail.com>
parent: Nacef LABIDI <nacef.l@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2009-08-26 13:32 UTC (permalink / raw)
To: Nacef LABIDI <nacef.l@gmail.com>; +Cc: pgsql-sql
Hello
postgres=# select * from foo;
+---+---+
| i | a |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
(3 rows)
Time: 0,654 ms
postgres=# select * from foo where i = ANY (string_to_array('1,3',',')::int[]);
+---+---+
| i | a |
+---+---+
| 1 | a |
| 3 | c |
+---+---+
(2 rows)
Time: 0,914 ms
regards
Pavel Stehule
2009/8/26 Nacef LABIDI <nacef.l@gmail.com>:
> Hi all,
>
> I want to write a function that takes as param a comma separated values
> string and perform a select matching these values.
>
> Here is the string '1,3,7,8'
>
> And I wan to perform a : SELECT * FROM my_table WHERE id IN (1, 3, 7, 8);
>
> Does anyone have a clue ?
>
> Thanks
>
> Nacef
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Selecting values from comma separated string
@ 2009-08-26 13:33 Pavel Stehule <pavel.stehule@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2009-08-26 13:33 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
2009/8/26 A. Kretschmer <andreas.kretschmer@schollglas.com>:
> In response to Nacef LABIDI :
>> Hi all,
>>
>> I want to write a function that takes as param a comma separated values string
>> and perform a select matching these values.
>>
>> Here is the string '1,3,7,8'
>>
>> And I wan to perform a : SELECT * FROM my_table WHERE id IN (1, 3, 7, 8);
>
> Use EXECUTE sql_string, see
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
>
> For instance, simple example, untested:
>
Hello
> create function foo (my_string) returns setof record as $$
> declare
> sql text;
> begin
> sql:='SELECT * FROM my_table WHERE id IN (' || $1 || ')';
> return query execute sql;
> end;
>
It's dangerous solution - there can be sql injection attack
regards
Pavel Stehule
> The variable sql contains the whole query, and then execute that.
>
> HTH, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> 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] 7+ messages in thread
* Re: Selecting values from comma separated string
@ 2009-08-26 14:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2009-08-26 14:05 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> In response to Nacef LABIDI :
>> I want to write a function that takes as param a comma separated values string
>> and perform a select matching these values.
> Use EXECUTE sql_string,
Safer to use string_to_array, for instance
... WHERE id = ANY(string_to_array('1,3,7,8', ',')::int[]) ...
Of course this just begs the question of why the OP doesn't use an
array in the first place.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Selecting values from comma separated string
@ 2009-08-26 14:13 Nacef LABIDI <nacef.l@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Nacef LABIDI @ 2009-08-26 14:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
Here I come again to ask how can I pass an array of values to a pgsql
function when I call this function from a delphi program for example.
Nacef
On Wed, Aug 26, 2009 at 3:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> > In response to Nacef LABIDI :
> >> I want to write a function that takes as param a comma separated values
> string
> >> and perform a select matching these values.
>
> > Use EXECUTE sql_string,
>
> Safer to use string_to_array, for instance
>
> ... WHERE id = ANY(string_to_array('1,3,7,8', ',')::int[]) ...
>
> Of course this just begs the question of why the OP doesn't use an
> array in the first place.
>
> regards, tom lane
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Nacef LABIDI
nacef.l@gmail.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Selecting values from comma separated string
@ 2009-08-26 14:58 Pavel Stehule <pavel.stehule@gmail.com>
parent: Nacef LABIDI <nacef.l@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2009-08-26 14:58 UTC (permalink / raw)
To: Nacef LABIDI <nacef.l@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
2009/8/26 Nacef LABIDI <nacef.l@gmail.com>:
> Here I come again to ask how can I pass an array of values to a pgsql
> function when I call this function from a delphi program for example.
>
the driver have to support it. But why?
simply you can use varchar and string_to_array function.
Pavel
> Nacef
>
>
>
> On Wed, Aug 26, 2009 at 3:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> "A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
>> > In response to Nacef LABIDI :
>> >> I want to write a function that takes as param a comma separated values
>> >> string
>> >> and perform a select matching these values.
>>
>> > Use EXECUTE sql_string,
>>
>> Safer to use string_to_array, for instance
>>
>> ... WHERE id = ANY(string_to_array('1,3,7,8', ',')::int[]) ...
>>
>> Of course this just begs the question of why the OP doesn't use an
>> array in the first place.
>>
>> regards, tom lane
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> --
> Nacef LABIDI
> nacef.l@gmail.com
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2009-08-26 14:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-26 13:19 Selecting values from comma separated string Nacef LABIDI <nacef.l@gmail.com>
2009-08-26 13:30 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-08-26 13:33 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-08-26 14:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-08-26 14:13 ` Nacef LABIDI <nacef.l@gmail.com>
2009-08-26 14:58 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-08-26 13:32 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox