Skip site navigation (1) Skip section navigation (2)

Re: plpgsql: help with RECORD type

From: Vyacheslav Kalinin <vka(at)mgcp(dot)com>
To: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql: help with RECORD type
Date: 2009-03-03 21:19:12
Message-ID: 9b1af80e0903031319m123f1f39r3f1886c7ba467970@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
There is no set variables in plpgsql. If you want to retrieve set of rows
for futher manipulation either use a CURSOR or ARRAY:

declare
  cur refcursor;
begin
  open cur for
    select * from table where ...;

  loop
    fetch cur into var1,var2...;
    exit when not found;
  end loop;



declare
  arr offsite_batch[];
  i int;
begin
   select array(
    select * from offsite_batch where ...
   ) into arr;

  for i in 1..array_upper(arr,1)
  loop
    var1 := arr[i].id;
    ...
  end loop;

Note that ARRAYs of composite type only appeared in 8.3.

I believe what you want to achieve can be done with simple query result
looping:

for batch in (
  select *  from offsite_batch
   where closed is NULL
   order by age(opened) ASC
) loop
  batch_found := true;
  IF NOT unique THEN
      RAISE WARNING 'more than one open batch found, closing...';
      UPDATE offsite_batch
             SET     closed = now()
        WHERE   batch_id = batch.id;
  ELSE
     latest_batch    := batch;
     unique          := FALSE;
  END IF;
end loop;

if not batch_found then
  RAISE NOTICE 'creating new offsite batch';
  ...
end if;

In response to

Responses

pgsql-novice by date

Next:From: Semyon ChaichenetsDate: 2009-03-03 21:51:23
Subject: Re: plpgsql: help with RECORD type
Previous:From: Andreas KretschmerDate: 2009-03-03 20:58:25
Subject: Re: plpgsql: help with RECORD type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group