From: | "Vadi" <mvadiraj(at)rediffmail(dot)com> |
---|---|
To: | <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | PostgreSQL equivalent of Oracle "member of" |
Date: | 2019-03-29 09:44:24 |
Message-ID: | 20190329094424.12000.qmail@f4mail-235-128.rediffmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2019-03-29 09:50:57 | Re: Key encryption and relational integrity |
Previous Message | Takuma Hoshiai | 2019-03-29 07:27:24 | what case does xid wraparound warning messages like example emit? |