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 _______________________________________________ PLUG mailing list [email protected] http://lists.pdxlinux.org/mailman/listinfo/plug
