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;
