RECORD.* doesn't work in Pl/PGSQL

"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;

On Wed, Apr 23, 2008 at 4:20 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

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

<highlight>

insert into t1 select 1, rec.*; -- throws ERROR: record type

has

<highlight>

not been registered
end loop;

end;
$$ language 'plpgsql';

you can do this:

<snip>

for rec in delete from t2 returning 1, * loop

Sorry... that 1 in SELECT 1, rec.* was a leftover from a trial workarond...
please ignore that.

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device