[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