[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    =?utf-8?B?UmU6IFBvc3RncmVTUUwgZXF1aXZhbGVudCBvZiBPcmFjbGUgJnF1b3Q7bWVtYmVyIG9mJnF1b3Q7?=
From:       "Vadi" <mvadiraj () rediffmail ! com>
Date:       2019-03-29 11:19:45
Message-ID: 1553853852.S.5890.27243.f4-234-218.1553858385.3134 () webmail ! rediffmail ! com
[Download RAW message or body]

Hi Mr Kellerer



Thanks a lot for your reply and suggestion.



I will check it out.



Regards

Vadi

Bengaluru

India



On Fri, 29 Mar 2019 15:34:12 +0530 Thomas Kellerer  wrote

> Vadi schrieb am 29.03.2019 um 10:44:



> I would like to know if there is any equivalent in PostgreSQL for the Oracle \
> "member of" syntax.



> 



> The usage is as shown below:



> 



> I have used the Oracle sample HR schema for the below example:



> 



> CREATE OR REPLACE TYPE params as table of varchar2 (100);



> /



> 



> CREATE OR REPLACE function in_list (in_list in varchar2) return params pipelined as



> param_list varchar2(4000) := in_list || ',';



> pos number;



> begin



> loop



> pos := instr(param_list, ',');



> exit when nvl(pos, 0) = 0;



> pipe row (trim(substr(param_list, 1, pos - 1)));



> param_list := substr(param_list, pos + 1);



> end loop;



> 



> return;



> end in_list;



> /



> 



> CREATE TABLE tname as



> SELECT listagg(first_name, ', ') within group (order by first_name) first_name FROM \
> employees;



> 



> SELECT * FROM tname;



> 



> SELECT * FROM employees



> WHERE first_name member of in_list(first_name);







I don't understand where the parameter to the in_list() functions comes from in the \
last query. 



As written it would be the value from employees.first_name, which is not a comma \
separated list, so it doesn't really make sense. 







I think what the in_list() function does, would be the equivalent to \
unnest/string_to_array







e.g.:







 select *



 from unnest(string_to_array('foo,bar', ',')) as t(name);







returns 







 name



 ----



 foo 



 bar 







If you just want to check if one string is contained in a comma separated list, you \
can use the ANY operator:







 where first_name = any (string_to_array('foo,bar', ','))







Thomas


[Attachment #3 (unknown)]

Hi Mr Kellerer<br />
<br />
Thanks a lot for your reply and suggestion.<br />
<br />
I will check it out.<br />
<br />
Regards<br />
Vadi<br />
Bengaluru<br />
India<br />
<br />
On Fri, 29 Mar 2019 15:34:12 +0530 Thomas Kellerer <spam_eater@gmx.net> wrote<br />
> Vadi schrieb am 29.03.2019 um 10:44:<br />
<br />
> I would like to know if there is any equivalent in PostgreSQL for the Oracle \
> "member of" syntax.<br />
<br />
> <br />
<br />
> The usage is as shown below:<br />
<br />
> <br />
<br />
> I have used the Oracle sample HR schema for the below example:<br />
<br />
> <br />
<br />
> CREATE OR REPLACE TYPE params as table of varchar2 (100);<br />
<br />
> /<br />
<br />
> <br />
<br />
> CREATE OR REPLACE function in_list (in_list in varchar2) return params pipelined \
> as<br />
<br />
> param_list varchar2(4000) := in_list || ',';<br />
<br />
> pos number;<br />
<br />
> begin<br />
<br />
> loop<br />
<br />
> pos := instr(param_list, ',');<br />
<br />
> exit when nvl(pos, 0) = 0;<br />
<br />
> pipe row (trim(substr(param_list, 1, pos - 1)));<br />
<br />
> param_list := substr(param_list, pos + 1);<br />
<br />
> end loop;<br />
<br />
> <br />
<br />
> return;<br />
<br />
> end in_list;<br />
<br />
> /<br />
<br />
> <br />
<br />
> CREATE TABLE tname as<br />
<br />
> SELECT listagg(first_name, ', ') within group (order by first_name) first_name FROM \
> employees;<br />
<br />
> <br />
<br />
> SELECT * FROM tname;<br />
<br />
> <br />
<br />
> SELECT * FROM employees<br />
<br />
> WHERE first_name member of in_list(first_name);<br />
<br />
<br />
<br />
I don't understand where the parameter to the in_list() functions comes from in the \
last query. <br /> <br />
As written it would be the value from employees.first_name, which is not a comma \
separated list, so it doesn't really make sense. <br /> <br />
<br />
<br />
I think what the in_list() function does, would be the equivalent to \
unnest/string_to_array<br /> <br />
<br />
<br />
e.g.:<br />
<br />
<br />
<br />
 select *<br />
<br />
 from unnest(string_to_array('foo,bar', ',')) as t(name);<br />
<br />
<br />
<br />
returns <br />
<br />
<br />
<br />
 name<br />
<br />
 ----<br />
<br />
 foo <br />
<br />
 bar <br />
<br />
<br />
<br />
If you just want to check if one string is contained in a comma separated list, you \
can use the ANY operator:<br /> <br />
<br />
<br />
 where first_name = any (string_to_array('foo,bar', ','))<br />
<br />
<br />
<br />
Thomas<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br>



[prev in list] [next in list] [prev in thread] [next in thread]