[PLUG] postgres equivalent to an oracle feature

Carlos Konstanski ckonstanski at pippiandcarlos.com
Sat Jan 22 04:38:39 UTC 2011


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;



More information about the PLUG mailing list