Thread: RECORD.* doesn't work in Pl/PGSQL

RECORD.* doesn't work in plpgsql, but NEW.* and OLD.* do in trigger functions created in plpgsql.<br /><br />    The
examplefunction process_emp_audit() on page <a
href="http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html">http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html</a>
,shows that we can use OLD.* and NEW.* as:<br /><br />INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;<br /><br
/>   but if I try to do the same thing in my own plpgsql function, it throws a runtime ERROR:<br /><br />create table
t1(a int, b char );<br /> create table t2( a int, b char );<br /><br clear="all" />create or replace function
log_rotate()returns void as $$<br />declare<br />  rec record;<br />begin<br /><br />    for rec in delete from t2
returning* loop<br />        insert into t1 select 1, rec.*;  -- throws ERROR:  record type has not been registered<br
/>    end loop;<br /><br />end;<br />$$ language 'plpgsql';<br /><br />select log_rotate();<br /><br />    Is this
intentional,or is this a bug?<br /><br />Best regards,<br />-- <br />gurjeet[.singh]@EnterpriseDB.com<br
/>singh.gurjeet@{gmail | hotmail | indiatimes | yahoo }.com<br /><br />EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/><br />Mail sent from my BlackLaptop device  

"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
> RECORD.* doesn't work in plpgsql, but NEW.* and OLD.* do in trigger
> functions created in plpgsql.

NEW and OLD have predetermined column sets (within any one trigger function).
A RECORD variable doesn't.
        regards, tom lane

On Tue, Apr 22, 2008 at 4:10 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> RECORD.* doesn't work in plpgsql, but NEW.* and OLD.* do in trigger
> functions created in plpgsql.
>
>     The example function process_emp_audit() on page
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html , shows
> that we can use OLD.* and NEW.* as:
>
> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
>
>     but if I try to do the same thing in my own plpgsql function, it throws
> a runtime ERROR:
>
> create table t1( a int, b char );
>  create table t2( a int, b char );
>
> create or replace function log_rotate() returns void as $$
> declare
>   rec record;
> begin
>
>     for rec in delete from t2 returning * loop
>         insert into t1 select 1, rec.*;  -- throws ERROR:  record type has
> not been registered
>      end loop;
>
> end;
> $$ language 'plpgsql';

you can do this:

create or replace function log_rotate() returns void as $$
declare  rec t1;
begin for rec in delete from t2 returning 1, * loop   insert into t1 select rec.*; end loop;
end;
$$ language plpgsql;