Re: Pipelined functions in Postgres
От | Shoaib Mir |
---|---|
Тема | Re: Pipelined functions in Postgres |
Дата | |
Msg-id | bf54be870609191504k72f69fb9s32f72484083c155f@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Pipelined functions in Postgres ("Milen Kulev" <makulev@gmx.net>) |
Список | pgsql-performance |
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)
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 9/20/06, Milen Kulev < makulev@gmx.net> 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 rowsetsin the consumer functions. I want to avoid writing to intermediate tables while performing ETL processing .Is this possible with SETOF functions ?Best RegardsMilen-----Original Message-----I think pipelined functions are code you can pretend is a database table.
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
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
В списке pgsql-performance по дате отправления: