Thread: problem with RECORD in a stored procedure

HI,

I have a strange problem with regard to the RECORD type. IN plpgsql language it 
doesn't seem to recognise the data type. Does anybody have any idea why? I've 
included a sample function and the terminal output to show what I mean. I've 
also included what should have gone into table orla.

Any help would be appreciated in sorting out this frustrating problem.

Shane

CREATE FUNCTION sinead() RETURNS integer AS 'DECLARE    a integer :=0;    b RECORD;     BEGIN    FOR b IN SELECT
MIN(total)from results LOOP        INSERT INTO orla(total) VALUES(b);    END LOOP;        RETURN a;        END;
 
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------

ballina=# select sinead();
ERROR:  Attribute 'b' not found

--------------------------------------------------------------------------
ballina=# select MIN(total) from results;min 
-----  2
(1 row)

---------------------------------------------------------------------------

On Fri, 8 Mar 2002, Dalton Shane wrote:

>
> HI,
>
> I have a strange problem with regard to the RECORD type. IN plpgsql language it
> doesn't seem to recognise the data type. Does anybody have any idea why? I've
> included a sample function and the terminal output to show what I mean. I've
> also included what should have gone into table orla.
>
> Any help would be appreciated in sorting out this frustrating problem.
>
> Shane
>
> CREATE FUNCTION sinead() RETURNS integer AS '
>     DECLARE
>         a integer :=0;
>         b RECORD;
>
>
>     BEGIN
>         FOR b IN SELECT MIN(total) from results LOOP
>             INSERT INTO orla(total) VALUES(b);

You need to specify an attribute of b to insert here (for example
b.min).

>         END LOOP;
>
>         RETURN a;
>
>
>     END;
> ' LANGUAGE 'plpgsql';

B is record type and you can't insert a record into a field.
You must rewrite your code like this:

FOR b IN SELECT MIN(total) as mintotal from results LOOP               INSERT INTO orla(total) VALUES(b.mintotal);
END LOOP;

DAQ