plpgsql: help with RECORD type

From: Semyon Chaichenets <pgsql-novice(at)semyon(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: plpgsql: help with RECORD type
Date: 2009-03-03 20:36:52
Message-ID: 6f81e0890903031236s31f8da41o945c684dde509f3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Andreas Kretschmer 2009-03-03 20:58:25 Re: plpgsql: help with RECORD type
Previous Message Lee, Mija 2009-03-03 17:48:52 Re: sig 6 on postgres server