[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