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

Pgpool Configuration & Failback

I would like to share the pgpool configuration, and it's failback mechanism in this post.

Hope it will be helpful to you in creating pgpool and it's failback setup.

Pgpool Installation & Configuration

1. Download the pgpool from below link(Latest version is 3.2.1).
    http://www.pgpool.net/mediawiki/index.php/Downloads


2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory.

3. Install the pgpool by executing the below commands:

./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/ --with­-pgsql­-libdir=/opt/PostgreSQL92/lib/ make make install 4. You can see the pgpool files in /opt/PostgreSQL92/bin location.
/opt/PostgreSQL92/bin $ ls clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool  …

pgBucket - A new concurrent job scheduler

Hi All,

I'm so excited to announce about my first contribution tool for postgresql. I have been working with PostgreSQL from 2011 and I'm really impressed with such a nice database.

I started few projects in last 2 years like pgHawk[A beautiful report generator for Openwatch] , pgOwlt [CUI monitoring. It is still under development, incase you are interested to see what it is, attaching the image here for you ],


pgBucket [Which I'm gonna talk about] and learned a lot and lot about PostgreSQL/Linux internals.

Using pgBucket we can schedule jobs easily and we can also maintain them using it's CLI options. We can update/insert/delete jobs at online. And here is its architecture which gives you a basic idea about how it works.


Yeah, I know there are other good job schedulers available for PostgreSQL. I haven't tested them and not comparing them with this, as I implemented it in my way.
Features are: OS/DB jobsCron style sytaxOnline job modificationsRequired cli options

N-Node Mutlimaster Replication With Bucardo...!

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL.

We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo.

Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..?

As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.}

Please follow the below steps to set up the 1 - 1 multi master replication.

1. Follow the below steps to get all the pre-requisites for the Bucardo.

yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here. tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For this, download…