Обсуждение: Pipelined functions in Postgres

Hello Lister,
I am curios whether I can emulate the Oracle pipelined functions functionality in PG too (using RETURN NEXT ). For more
information and examples about Oracle pipelined functions see:
http://asktom.oracle.com/pls/ask/f?p=4950:8:8127757633768425921::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4447489221109

I have used  pipeline functions in DWH enviromnent  with success and would like
To use similar concept in PG too.

Any help, examples , links and  shared experiences would be greately appreciated.

Best Regards.
Milen

I think pipelined functions are code you can pretend is a database table.

For example you can do it like this in Oracle:

select * from PLSQL_FUNCTION;

You can achieve something similar in PostgreSQL using RETURN SETOF functions like this:

CREATE OR REPLACE FUNCTION test_pipe (int)
    RETURNS SETOF RECORD AS
$$
DECLARE
    v_rec    RECORD;
BEGIN
    FOR temp_rec IN (SELECT col FROM table where col > 10)
    LOOP
        RETURN NEXT v_rec;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

This function can be called like this:

SELECT * FROM test_pipe(10) AS tbl (col int);
 
Hope this helps...

Thanks,
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Shoaib Mir
Sent: Tuesday, September 19, 2006 11:05 PM
To: Milen Kulev
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Pipelined functions in Postgres

I think pipelined functions are code you can pretend is a database table.

For example you can do it like this in Oracle:

select * from PLSQL_FUNCTION;

You can achieve something similar in PostgreSQL using RETURN SETOF functions like this:

CREATE OR REPLACE FUNCTION test_pipe (int)
    RETURNS SETOF RECORD AS
$$
DECLARE
    v_rec    RECORD;
BEGIN
    FOR temp_rec IN (SELECT col FROM table where col > 10)
    LOOP
        RETURN NEXT v_rec;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

This function can be called like this:

SELECT * FROM test_pipe(10) AS tbl (col int);
 
Hope this helps...

Thanks,
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 9/20/06, Milen Kulev <makulev@gmx.net> wrote:

Hello Lister,
I am curios whether I can emulate the Oracle pipelined functions functionality in PG too (using RETURN NEXT ). For more
information and examples about Oracle pipelined functions see:
http://asktom.oracle.com/pls/ask/f?p=4950:8:8127757633768425921::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4447489221109

I have used  pipeline functions in DWH enviromnent  with success and would like
To use similar concept in PG too.

Any help, examples , links and  shared experiences would be greately appreciated.

Best Regards.
Milen


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

-----Original Message-----
From: Talha Khan [mailto:talha.amjad@gmail.com]
Sent: Tuesday, September 19, 2006 11:08 PM
To: Milen Kulev
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Pipelined functions in Postgres

Hi Milen,

 

Pipelined function is a code that acts like a database table.

 

Inorder to use this functionality in postgres you would need to write the function like this

 

CREATE OR REPLACE FUNCTION get_test_data (numeric)
    RETURNS SETOF RECORD AS
$$
DECLARE
    temp_rec    RECORD;
BEGIN
    FOR temp_rec IN (SELECT ename FROM emp WHERE sal > $1)
    LOOP
        RETURN NEXT temp_rec;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;
 

now inorder to call this function you would write the code as follows

 

SELECT * FROM get_test_data(1000) AS t1 (emp_name VARCHAR);
 

 

Regards

Talha Amjad

 

On 9/19/06, Milen Kulev <makulev@gmx.net> wrote:

Hello Lister,
I am curios whether I can emulate the Oracle pipelined functions functionality in PG too (using RETURN NEXT ). For more
information and examples about Oracle pipelined functions see:
http://asktom.oracle.com/pls/ask/f?p=4950:8:8127757633768425921::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4447489221109

I have used  pipeline functions in DWH enviromnent  with success and would like
To use similar concept in PG too.

Any help, examples , links and  shared experiences would be greately appreciated.

Best Regards.
Milen


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

I dont think so that will be possible using SETOF function ...

You might have to partition the current query and this way can distribute the full load of the query if there is too much data invovled.

Thanks,
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On Tue, 2006-09-19 at 23:22 +0200, Milen Kulev wrote:
> Hello Shoaib,
> I know the SETOF funcitons. I want to  simulate (somehow)
> producer/consumer relationship with SETOF(pipelined) functions.  The
> first  (producer )function generates records (just like your test_pipe
> function), and the second function consumers the records , produced by
> the first function. The second function can be rows/records producer
> for another consumer functions e.g. it should looks like(or similar)
> select * from consumer_function(  producer_function(param1,
> param2, ...));
>
> What I want to achieve is to impelement some ETL logic
> in consumer_functions (they could be chained, of course).
> The main idea is to read source  DWH tables once (in
>  producer_function, for example), and to process the rowsets
> in the consumer functions. I want to avoid writing to intermediate
> tables while performing ETL processing .
> Is this possible with SETOF functions ?
>

Functions cannot take a relation as a parameter.

Why not create a single function that does what you need it to do? You
can write such a function in the language of your choice, including C,
perl, PL/pgSQL, among others. That gives you a lot of power to do what
you need to do in a single pass, without passing the results on to other
functions.

If you provide an example of what you need to be able to do maybe
someone on this list knows a way to do it with one function call.

Also, I'll point out that what you want to do is very similar to using
typical relational constructs. Consider whether sub-selects or
aggregates in conjunction with set-returning functions can achieve what
you want. PostgreSQL is smart enough to only read the big table once if
possible.

Regards,
    Jeff Davis