ERROR: record "previousrec" is unassigned yet - Mailing list pgsql-bugs

I am writing a script that loops through a series of records, comparing each
to a previous record, and writing the changes to a new table.  It selects
the current record into a RECORD variable (CurrentRec), and then attempts to
load the previous record into a second RECORD variable (PreviousRec).
When there is no previous record, PreviousRec is filled with NULLs.  This is
as expected, and I can use a RAISE NOTICE that prints the NULLs just fine.
However, when I try and pass a field from PreviousRec into another function
that compares the fields and makes the entry to the new table, I get the
following error:

ERROR:  record "previousrec" is unassigned yet

I can usually pass NULL fields into this function without any problems (see
entry in messages from example script NOTICE:  previous id is 2: fields
<NULL>,<NULL>).  This seems to me to be a bug, since the RAISE NOTICE works
fine, and the data type of the fields should be known from the SELECT INTO
statement.  The documentation states that 'If the SELECT query returns zero
rows, null values are assigned to the target(s).' (PostgreSQL 7.3.2
Programmer’s Guide 19.5.2. SELECT INTO).

An example script is included below.  My actual script is much more involved
than this, and I have created a workaround that involves declaring a
variable for each field in PreviousRec, but it is far less elegant than
using a RECORD variable.

Regards,
Toby Long-Leather.


Messages from example script:

Query result with 0 rows discarded.
Query result with 0 rows discarded.
Query result with 0 rows discarded.
Query result with 0 rows discarded.
Query result with 0 rows discarded.
Query result with 0 rows discarded.
Query result with 0 rows discarded.
NOTICE:  current id is 4: fields <NULL>,Result4
NOTICE:  previous id is 3: fields Result3,<NULL>
NOTICE:  sub: <NULL> Result3
NOTICE:  sub: Result4 <NULL>
NOTICE:  current id is 3: fields Result3,<NULL>
NOTICE:  previous id is 2: fields <NULL>,<NULL>
NOTICE:  sub: Result3 <NULL>
NOTICE:  sub: <NULL> <NULL>
NOTICE:  current id is 2: fields <NULL>,<NULL>
NOTICE:  previous id is 1: fields Result1,Result2
NOTICE:  sub: <NULL> Result1
NOTICE:  sub: <NULL> Result2
NOTICE:  current id is 1: fields Result1,Result2
NOTICE:  previous id is <NULL>: fields <NULL>,<NULL>
WARNING:  Error occurred while executing PL/pgSQL function test
WARNING:  line 9 at assignment
Query result with 0 rows discarded.

ERROR:  record "previousrec" is unassigned yet


Example script:

-- DROP TABLE Test_Table;

CREATE TABLE Test_Table (
  ID integer,
  Field1 varchar(255),
  Field2 varchar(255)
);

INSERT INTO Test_Table VALUES (1, 'Result1', 'Result2');
INSERT INTO Test_Table VALUES (2, NULL, NULL);
INSERT INTO Test_Table VALUES (3, 'Result3', NULL);
INSERT INTO Test_Table VALUES (4, NULL, 'Result4');

CREATE OR REPLACE FUNCTION test_sub(varchar, varchar)
  RETURNS int4 AS
'DECLARE
  Field1 ALIAS FOR $1;
  Field2 ALIAS FOR $2;
BEGIN
  RAISE NOTICE \'sub: % %\', Field1, Field2;
  RETURN 1;
END;
' LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION test()
  RETURNS int4 AS
'DECLARE
  CurrentRec RECORD;
  PreviousRec RECORD;
BEGIN
  FOR CurrentRec IN SELECT ID, Field1, Field2, (SELECT sub.ID FROM
Test_Table AS sub WHERE sub.ID < Test_Table.ID ORDER BY sub.ID DESC LIMIT 1)
AS Previous_ID FROM Test_Table ORDER BY ID DESC LOOP
    RAISE NOTICE \'current id is %: fields %,%\', CurrentRec.ID,
CurrentRec.Field1, CurrentRec.Field2;
    SELECT INTO PreviousRec * FROM Test_Table WHERE ID =
CurrentRec.Previous_ID;
    RAISE NOTICE \'previous id is %: fields %,%\', PreviousRec.ID,
PreviousRec.Field1, PreviousRec.Field2;
    PERFORM test_sub(CurrentRec.Field1, PreviousRec.Field1);
    PERFORM test_sub(CurrentRec.Field2, PreviousRec.Field2);
  END LOOP;
  RETURN 1;
END;
' LANGUAGE 'plpgsql' VOLATILE;


SELECT test();