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

List:       postgresql-general
Subject:    =?utf-8?B?UG9zdGdyZVNRTCBlcXVpdmFsZW50IG9mIE9yYWNsZSAibWVtYmVyIG9mIg==?=
From:       "Vadi" <mvadiraj () rediffmail ! com>
Date:       2019-03-29 9:44:24
Message-ID: 20190329094424.12000.qmail () f4mail-235-128 ! rediffmail ! com
[Download RAW message or body]

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


[Attachment #3 (unknown)]

Hi all<br />
<br />
I would like to know if there is any equivalent in PostgreSQL for the Oracle "member of" syntax.<br />
<br />
The usage is as shown below:<br />
<br />
I have used the Oracle sample HR schema for the below example:<br />
<br />
CREATE OR REPLACE TYPE params as table of varchar2 (100);<br />
/<br />
<br />
CREATE OR REPLACE function in_list (in_list  in  varchar2) return params pipelined	as<br />
            param_list  varchar2(4000) := in_list || ',';<br />
            pos   number;<br />
            begin<br />
            loop<br />
            pos := instr(param_list, ',');<br />
            exit when nvl(pos, 0) = 0;<br />
            pipe row (trim(substr(param_list, 1, pos - 1)));<br />
            param_list := substr(param_list, pos + 1);<br />
            end loop;<br />
<br />
            return;<br />
            end in_list;<br />
<br />
/<br />
<br />
CREATE TABLE tname as<br />
SELECT listagg(first_name, ', ') within group (order by first_name) first_name FROM employees;<br />
<br />
SELECT * FROM tname;<br />
<br />
SELECT * FROM employees<br />
WHERE first_name member of in_list(first_name);<br />
<br />
Any help is much appreciated.<br />
<br />
Thanks in advance.<br />
<br />
Regards<br />
Vadi<br />
<br>

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