Обсуждение: PostgreSQL equivalent of Oracle "member of"

Hi all

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);

Any help is much appreciated.

Thanks in advance.

Regards
Vadi

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
makesense. 
 

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

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