Skip to main content

::Pipelined in Oracle as well in PostgreSQL::

Pipelined Table Functions:- [ORACLE]
===========================

If you want to return multiple rows to the calling environment, then piplined table functions is prefred. It will increase the dbperformance as well.

Ex:-

Step 1:
-----------
CREATE TABLE EMP(EMPNO INT,ENAME VARCHAR2(10),SAL INT);

Step 2:
-----------
Insert sample data.

Step 3:
-----------
Create an object for the row type casting.
CREATE OR REPLACE TYPE emp_row
AS OBJECT
(
empno INT,
ename VARCHAR2(20),
SAL INT
);

Step 4:
-----------
Create a Return Type for the pipelined function.
CREATE OR REPLACE TYPE emp_table_type
AS TABLE OF emp_row;

Step 5:
-----------
CREATE OR REPLACE FUNCTION emp_pipe_function RETURN emp_table_type PIPELINED IS
BEGIN
FOR rec in (select * from emp)
LOOP
PIPE ROW (emp_row(rec.empno,rec.ename,rec.sal));
END LOOP;
RETURN;
END;


Step 6:
----------
SQL> select * from table(emp_pipe_function);

EMPNO ENAME SAL
---------- -------------------- ----------
1 Dinesh 1000
2 MANOJA 2000
3 SREE VANI 3000
4 NIVAS 4000

SQL> select * from emp;

EMPNO ENAME SAL
---------- ---------- ----------
1 Dinesh 1000
2 MANOJA 2000
3 SREE VANI 3000
4 NIVAS 4000

Same Case In PostgreSQL
=========================

Step 1:
---------
CREATE TABLE EMP(ENO INT,ENAME VARCHAR,SAL INT);

Step 2:
---------
Insert Sample Rows

Step 3:
---------
CREATE OR REPLACE FUNCTION EMP_FUNC() RETURNS SETOF PUBLIC.EMP
AS
$$
SELECT * FROM EMP;
$$
LANGUAGE SQL;

(OR)

CREATE OR REPLACE FUNCTION EMP_FUNC() RETURNS SETOF PUBLIC.EMP
AS
$$
BEGIN
RETURN QUERY SELECT * FROM EMP;
END;
$$
LANGUAGE PLPGSQL;

(OR)

CREATE OR REPLACE FUNCTION EMP_FUNC() RETURNS SETOF PUBLIC.EMP AS
$$
DELCARE
T RECORD;
BEGIN
FOR  T IN SELECT * FROM EMP
LOOP
RETURN NEXT T;
END LOOP;
END;
$$
LANGUAGE PLPGSQL;

Step 4:
----------
postgres=# SELECT * FROM EMP_FUNC();
eno | ename | sal
-----+-----------+------
1 | Dinesh | 1000
2 | MANOJA | 2000
3 | SREE VANI | 3000
4 | NIVAS | 4000
(4 rows)

postgres=# SELECT * FROM EMP;
eno | ename | sal
-----+-----------+------
1 | Dinesh | 1000
2 | MANOJA | 2000
3 | SREE VANI | 3000
4 | NIVAS | 4000
(4 rows)

Comments

  1. Hello. First sorry for my english and for my keyboard (I'm writting with my phone).
    I'd like "throw" more than a single line, like i did with Oracle PL/SQL, for each iteration of the loop. Here is an example:
    for rec in cursor
    loop
    row.col1 := rec.col1;
    ...
    if rec.colX = 'TRUE'
    then
    row.colWhen = 'Morning';
    pipe row (row);
    end if;
    if rec.colY = 'TRUE'
    then
    row.colWhen = 'Afternoom';
    pipe row (row);
    end if;
    if rec.colZ = 'TRUE'
    then
    row.colWhen = 'Night';
    pipe row (row);
    end if;
    end loop;
    As you see, the script is able to send from 0 to 3 lines at every iteration of the cursor.
    I'm too noob in plpgsql to do it. I need some help. Can you and can plpgsql do it?
    Thanks a lot.

    ReplyDelete
    Replies
    1. Precision: cols colX, colY and colZ are result of decode / case function of which pl language is used.

      Delete
  2. Does it work for you.

    CREATE OR REPLACE FUNCTION EMP_FUNC() RETURNS SETOF TEXT AS
    $$
    DECLARE
    T RECORD;
    T1 RECORD;
    BEGIN
    FOR T IN SELECT * FROM EMP
    LOOP
    IF T.SAL <=1000 THEN
    SELECT 'LOW' INTO T1;
    ELSIF T.SAL <=2000 THEN
    SELECT 'MEDIUM' INTO T1;
    ELSIF T.SAL <=3000 THEN
    SELECT 'HIGH' INTO T1;
    ELSIF T.SAL <=4000 THEN
    SELECT 'VERY HIGH' INTO T1;
    END IF;
    RETURN NEXT T1;
    END LOOP;
    RETURN;
    END;
    $$
    LANGUAGE PLPGSQL;

    Thanks,
    Dinesh

    ReplyDelete
  3. Hi.
    Thanks for your answer.
    Your example does'nt return what I expect.
    In your example, I'd like every t.sal <=2000 return 2 lines (LOW and MEDIUM), every t.sal <=3000 return 3 lines ( LOW, MEDIUM and HIGH) and of course, every t.sal <= 4000 return 4 lines...
    That's what my PL/SQL code does, but I don't know how to do the same with PLPGSQL. Every «RETURN NEXT» sends the last value of T1.
    I think when Oracle reads an instruction PIPE ROW, it sends a ROW. If only I could write the equivalent in each IF - END IF block...

    What do you think about it? My code is too "poor", isn't it?

    ReplyDelete
  4. I found a cheat!!!

    CREATE OR REPLACE FUNCTION EMP_FUNC() RETURNS SETOF TEXT AS
    $$
    DECLARE
    T RECORD;
    BEGIN
    FOR T IN SELECT * FROM EMP ORDER BY SAL DESC
    LOOP
    IF T.SAL <=1000 THEN
    RETURN QUERY SELECT 'LOW';
    ELSIF T.SAL <=2000 THEN
    RETURN QUERY SELECT 'LOW';
    RETURN QUERY SELECT 'MEDIUM';
    ELSIF T.SAL <=3000 THEN
    RETURN QUERY SELECT 'LOW';
    RETURN QUERY SELECT 'MEDIUM';
    RETURN QUERY SELECT 'HIGH';
    ELSIF T.SAL <=4000 THEN
    RETURN QUERY SELECT 'LOW';
    RETURN QUERY SELECT 'MEDIUM';
    RETURN QUERY SELECT 'HIGH';
    RETURN QUERY SELECT 'VERY HIGH';
    END IF;
    END LOOP;
    RETURN;
    END;
    $$
    LANGUAGE PLPGSQL;

    I can't find, now, a better way to do it. I dislike this code, but i does what I want.

    Do you have better?

    ReplyDelete
  5. Hi Fred,

    Sorry for the late reply, i was bit busy in some other stuff.

    Does it work for you.

    CREATE OR REPLACE FUNCTION EMP_FUNC1() RETURNS SETOF TEXT[] AS
    $$
    DECLARE
    T RECORD;
    BEGIN
    FOR T IN SELECT * FROM EMP ORDER BY SAL DESC
    LOOP
    IF T.SAL <=1000 THEN
    RETURN NEXT ARRAY['LOW'];
    ELSIF T.SAL <=2000 THEN
    RETURN NEXT ARRAY['LOW', 'MEDIUM'];
    ELSIF T.SAL <=3000 THEN
    RETURN NEXT ARRAY['LOW', 'MEDIUM', 'HIGH'];
    END IF;
    END LOOP;
    RETURN;
    END;
    $$
    LANGUAGE PLPGSQL;

    postgres=# SELECT * from emp_func1();
    emp_func1
    -------------------
    {LOW,MEDIUM,HIGH}
    {LOW,MEDIUM}
    {LOW}
    (3 rows)


    postgres=# SELECT unnest(emp_func1());
    unnest
    --------
    LOW
    MEDIUM
    HIGH
    LOW
    MEDIUM
    LOW
    (6 rows)

    Thanks,
    Dinesh

    ReplyDelete

Post a Comment

Popular posts from this blog

How To Send E-Mail From PostgreSQL

Hi , If you want to send E-Mails from PostgreSQL, then use the below Python 3.2 Script as below. I have used ActivePython 3.2 with PostgreSQL 9.1 for sending E-Mails from PostgreSQL. If you want to configure the Python 3.2 with PostgreSQL 9.1 then, please refer the below steps. http://manojadinesh.blogspot.in/2012/06/fatal-python-error-pyinitialize-unable.html Once, your Python 3.2 successful then follow the below steps to send an e-mail. Step 1 ===== postgres=# CREATE OR REPLACE FUNCTION public.send_email(_from Text,_password Text,smtp Text,port INT,receiver text, subject text, send_message text) RETURNS TEXT  LANGUAGE plpython3u AS $function$ import smtplib sender = _from receivers = receiver message = ("From: %s\nTo: %s\nSubject: %s\n\n %s"  % (_from,receiver,subject,send_message)) try:   smtpObj = smtplib.SMTP(smtp,port)   smtpObj.starttls()   smtpObj.login(_from, _password)   smtpObj.sendmail(sender, receivers,message)   print ('Successf

DBLINK in PostgreSQL

Hi, Find the below DBLINK concepts in PostgreSQL, PostgresPlus as well. PostgreSQL ---------- Foreign Data Wrapper -------------------- This is the one utility which encapsulates the data from the different Database servers using Handler functions. Ex:- In PG 9.1, we can encapsulates the data from My Sql and store to PostgreSQL using handlers. CREATE FOREIGN DATA WRAPPER HANDLER VALIDATOR ; Currently(<= 9.0) PostgreSQL compatible with only remote PostgreSQL. From 9.1 onwards, we can make the PostgreSQL to communicate with SQL Server/My SQL through these wrappers. Servers ------- This is the one object which is having the Data wrapper methodology as well the remote host connectivity details. Ex:- CREATE SERVER FOREIGN DATA WRAPPER OPTIONS (address , port ,dbname ); User Mapping ------------ This is the one object which maps the current dbserver user/role to remote dbserver user. Ex:-CREATE USER MAPPING FOR SERVER OPTIONS (username , password ); DBLINK

PostgreSQL High Performance Cookbook

Sharing knowledge which I have gained from last 6 years. So glad to be part of PostgreSQL High Performance Cookbook, where I have discussed all the knowledge I have gained from PostgreSQL database. PostgreSQL High Performance Cookbook Working with PostgreSQL from last 6 years, I have gained so much of knowledge about database management systems. Being a DBA for several years, I explored so many tools which work great with PostgreSQL database. During this 6 years journey, I got a chance to meet many wonderful peoples who guided me very well. I would like to say thanks to everyone who taught me PostgreSQL database in soft/hard ways :-). Also, would like to say thanks to every PostgreSQL developer, and authors and bloggers, from where I have learned many more things. Finally thanks to OpenSCG team who always treated me as a brother than an employee. :-) Thanks to my wife manoja  for her wonderful support, and my friend Baji Shaik for his help in writing the content.