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

pgBucket v1.0 is ready

pgBucket v1.0 pgBucket v1.0 (concurrent job scheduler for PostgreSQL) is released. This version is more stable and fixed the issues which was observed in the previous beta releases.
Highlights of this tool are Schedule OS/DB level jobsCron style syntax {Schedule up to seconds}On fly job modificationsInstant daemon status by retrieving live job queue, job hashEnough cli options to deal with all the configured/scheduled job Here is the URL for the pgBucket build/usage instructions. https://bitbucket.org/dineshopenscg/pgbucket
I hope this tool will be helpful for the PostgreSQL users to get things done in the scheduled time. Note: This tool requires c++11{gcc version >= 4.9.3} to compile.
--Dinesh