Thread: plpgsql: help with RECORD type

Dear List,

I am trying to write a simple plpgsql function that would select a set of rows based on a fixed property, then operate on this returned set. There is obviously something fundamental I don't understand about variables in plpgsql; I get the error message

ERROR:  syntax error at or near "$1" at character 3
QUERY:    $1
CONTEXT:  SQL statement in PL/PgSQL function "latest_offsite_batch" near line 16
LINE 1:   $1
          ^

I believe this references line 19 in the listing below, where I try reading my record variable.
I have done my share of RTFM and experimenting, but at this point I just need someone to show the error of my ways. Please help.

I am using postgres v. 8.1.13 on linux, more specifically the stock package that comes with ubuntu "dapper" distro, 8.1.13-0ubuntu0.6.06.


  1 CREATE OR REPLACE FUNCTION latest_offsite_batch()
  2         RETURNS integer
  3 AS $$
  4 DECLARE
  5         latest_batch    integer;
  6         open_batches    offsite_batch;
  7         unique          boolean := TRUE;
  8         batch           RECORD;
  9 BEGIN
 10         SELECT  INTO open_batches
 11                 * FROM offsite_batch
 12                 WHERE closed is NULL
 13                 ORDER BY age(opened) ASC;
 14
 15         IF NOT FOUND THEN
 16                 RAISE NOTICE 'creating new offsite batch';
 17                 SELECT INTO latest_batch create_offsite_batch();
 18         ELSE
 19                 FOR batch IN open_batches
 20                 LOOP
 21                         IF NOT unique THEN
 22                                 RAISE WARNING 'more than one open batch found, closing...';
 23                                 UPDATE offsite_batch
 24                                         SET     closed = now()
 25                                         WHERE   batch_id = batch;
 26                         ELSE
 27                                 latest_batch    := batch;
 28                                 unique          := FALSE;
 29                         END IF;
 30                 END LOOP;
 31         END IF;
 32         RETURN latest_batch;
 33 END
 34 $$ LANGUAGE 'PLPgSQL' VOLATILE;

Semyon Chaichenets

Semyon Chaichenets <pgsql-novice@semyon.net> wrote:

> Dear List,
>
> I am trying to write a simple plpgsql function that would select a set of rows
> based on a fixed property, then operate on this returned set. There is
> obviously something fundamental I don't understand about variables in plpgsql;
> I get the error message
>
> ERROR:  syntax error at or near "$1" at character 3
> QUERY:    $1
> CONTEXT:  SQL statement in PL/PgSQL function "latest_offsite_batch" near line
> 16
> LINE 1:   $1
>           ^
>
> I believe this references line 19 in the listing below, where I try reading my
> record variable.
> I have done my share of RTFM and experimenting, but at this point I just need
> someone to show the error of my ways. Please help.
>
> I am using postgres v. 8.1.13 on linux, more specifically the stock package
> that comes with ubuntu "dapper" distro, 8.1.13-0ubuntu0.6.06.
>
>
>   1 CREATE OR REPLACE FUNCTION latest_offsite_batch()
>   2         RETURNS integer
>   3 AS $$
>   4 DECLARE
>   5         latest_batch    integer;

latest_batch integer;


>   6         open_batches    offsite_batch;
>   7         unique          boolean := TRUE;
>   8         batch           RECORD;

batch RECORD;

>   9 BEGIN
>  10         SELECT  INTO open_batches
>  11                 * FROM offsite_batch
>  12                 WHERE closed is NULL
>  13                 ORDER BY age(opened) ASC;
>  14
>  15         IF NOT FOUND THEN
>  16                 RAISE NOTICE 'creating new offsite batch';
>  17                 SELECT INTO latest_batch create_offsite_batch();
>  18         ELSE
>  19                 FOR batch IN open_batches
>  20                 LOOP
>  21                         IF NOT unique THEN
>  22                                 RAISE WARNING 'more than one open batch
> found, closing...';
>  23                                 UPDATE offsite_batch
>  24                                         SET     closed = now()
>  25                                         WHERE   batch_id = batch;
>  26                         ELSE
>  27                                 latest_batch    := batch;

Integer := RECORD?

I think, thats the error.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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
end loop;

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

Vyacheslav,

thank you so much!

Semyon Chaichenets