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

On Wed, Apr 23, 2008 at 4:20 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Tue, Apr 22, 2008 at 4:10 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device