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

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 (view raw or flat)
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

pgsql-novice by date

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

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