On 01/23/2011 10:15 AM, Michael Ewan wrote: > 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 >
Almost, but not quite, the same thing. However, I did find a solution. Instead of saying: OPEN rec FOR 'SELECT ...' you want to say: OPEN rec FOR EXECUTE 'SELECT ...' Then it works just like Oracle. Thanks! Carlos _______________________________________________ PLUG mailing list [email protected] http://lists.pdxlinux.org/mailman/listinfo/plug
