[PLUG] postgres equivalent to an oracle feature
Michael Ewan
mhewan1 at comcast.net
Sun Jan 23 17:15:12 UTC 2011
On 1/21/2011 8:38 PM, Carlos Konstanski wrote:
> Below is a snippet from an oracle PLSQL function that runs a dynamic SELECT
> statement and uses the results in a record loop. I'm trying to find out how
> to do this in postgresql. It's the OPEN ... FOR 'SELECT something' LOOP part
> I'm interested in.
>
> Thanks,
>
> Carlos
>
> OPEN l_rec FOR
> 'SELECT c.cable_id AS row_id,
> c.cable_id AS cable_id,
> replace(c.cable_num, '' '', '''') AS cable_number,
> c.cable_desc AS cable_description,
> ct.cable_type_name AS cable_type,
> uc.cbl_udf_c01 AS voltage_level,
> replace(c.side_1_desc, '' '', '''') AS from_panel,
> replace(c.side_2_desc, '' '', '''') AS to_panel
> FROM ' || p_schema || '.cable c
> JOIN ' || p_schema || '.cable_type ct
> ON c.cable_type_id = ct.cable_type_id
> JOIN ' || p_schema || '.udf_cable uc
> ON c.cable_id = uc.cable_id
> WHERE c.def_flg = ''N''
> AND c.cable_id> 0';
> LOOP
> FETCH l_rec INTO o_rec.row_id,
> o_rec.cable_id,
> o_rec.cable_number,
> o_rec.cable_description,
> o_rec.cable_type,
> o_rec.voltage_level,
> o_rec.from_panel,
> o_rec.to_panel;
> EXIT WHEN l_rec%NOTFOUND;
> PIPE ROW(o_rec);
> END LOOP;
> _______________________________________________
Try this example from the PostgreSQL docs...
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
Also try cross posting to the pdxpug (PDX PostgreSQL User Group) list,
details at ...
http://pugs.postgresql.org/pdx
More information about the PLUG
mailing list