Обсуждение: Selecting values from comma separated string

<div dir="ltr">Hi all,<br /><br />I want to write a function that takes as param a comma separated values string and
performa select matching these values.<br /><br />Here is the string '1,3,7,8'<br /><br />And I wan to perform a :
SELECT* FROM my_table WHERE id IN (1, 3, 7, 8);<br /><br />Does anyone have a clue ?<br /><br />Thanks<br /><br
/>Nacef<br/></div> 

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

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
>

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
>

"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

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
>