Postgres Pro
Downloads
Home   >   mailing lists

Thread: Loop plpgsql recordset

Loop plpgsql recordset

From
"Furesz Peter"
Date:
Hello,

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;

Re: Loop plpgsql recordset

From
George Weaver
Date:
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

Re: Loop plpgsql recordset

From
Alban Hertroys
Date:
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 //

Re: Loop plpgsql recordset

From
"Angva"
Date:
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 Jan 25, 2:46 pm, fureszpe...@srv.hu ("Furesz Peter") wrote:
> Hello,
>
> 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;