Loop plpgsql recordset

This is how I loop through a record:

for rec in (select * from yourtable where somevar=3) loop
--output the record
raise notice '%', rec.somevar
end loop;

------

Mark

On Thursday, January 25 Furesz Peter wrote:

How can I loop a PL/PgSQL recorset variable? The example:

DECLARE
v_tmp_regi RECORD;
v_tmp RECORD;
BEGIN
SELECT * INTO v_tmp_regi FROM sulyozas_futamido sf WHERE
sf.termekfajta_id=
a_termekfajta_id AND sf.marka_id=a_marka_id;

DELETE FROM sulyozas_futamido;

FOR v_tmp IN v_tmp_regi LOOP
--I would like to work here with the old recordset!
END LOOP;
^^^^^^^^^^^^^^
-- This is not working !!!

END;

Its difficult to determine what you're trying to accomplish in the loop, but you may want to refer to 37.7.4. Looping Through Query Results in http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

Note that DELETE FROM sulyozas_futamido; will delete ALL records in sulyozas_futamido!

Perhaps:

FOR v_tmp IN SELECT * FROM sulyozas_futamido sf
WHERE sf.termekfajta_id = a_termekfajta_id AND sf.marka_id=a_marka_id;

LOOP

DELETE FROM sulyozas_futamido WHERE (some condition related to v_tmp???)

Work with old record now in v_tmp

END LOOP;

Regards,
George

Furesz Peter wrote:

FOR v_tmp IN v_tmp_regi LOOP
--I would like to work here with the old recordset!
END LOOP;
^^^^^^^^^^^^^^
-- This is not working !!!

How do you expect to loop one record?

In a recent thread (with a remarkably similar question - maybe you asked
this before?) it was suggested to move the SELECT statement to where the
RECORD type variable is. FOR .. IN .. expects a query.

Maybe a result set would work, but a RECORD type variable is *not* a
result set. Or a query, for that matter.

Fixing this is still not going to work for anything but the first result
though, as you delete the entire table constents right after it.

Maybe you should start by explaining what your function is supposed to do?

For lack of that, I think you meant this:

DECLARE
v_tmp_regi RECORD;
BEGIN
FOR v_tmp_regi IN SELECT * FROM sulyozas_futamido sf WHERE
sf.termekfajta_id=
a_termekfajta_id AND sf.marka_id=a_marka_id;

-- Work here with the old recordset
END LOOP;

DELETE FROM sulyozas_futamido;
END;

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //