Re: plpgsql: help with RECORD type

Semyon Chaichenets <pgsql-novice(at)semyon(dot)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°