From: | "Vadi" <mvadiraj(at)rediffmail(dot)com> |
---|---|
To: | <spam_eater(at)gmx(dot)net> |
Cc: | <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: PostgreSQL equivalent of Oracle "member of" |
Date: | 2019-03-29 11:19:45 |
Message-ID: | 1553853852.S.5890.27243.f4-234-218.1553858385.3134@webmail.rediffmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-03-29 12:58:11 | Re: stale WAL files? |
Previous Message | Thomas Kellerer | 2019-03-29 10:03:53 | Re: PostgreSQL equivalent of Oracle "member of" |