Lists: | pgsql-hackers |
---|
From: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | "PGSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RECORD.* doesn't work in Pl/PGSQL |
Date: | 2008-04-22 20:10:39 |
Message-ID: | 65937bea0804221310s13810497rde82a85cc4c2f288@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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';
select log_rotate();
Is this intentional, or is this a bug?
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
Cc: | "PGSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: RECORD.* doesn't work in Pl/PGSQL |
Date: | 2008-04-22 21:41:24 |
Message-ID: | 27637.1208900484@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)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
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
Cc: | "PGSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: RECORD.* doesn't work in Pl/PGSQL |
Date: | 2008-04-22 22:50:40 |
Message-ID: | b42b73150804221550l261da8feqf75f1c610c410344@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
> 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;
From: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | "PGSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: RECORD.* doesn't work in Pl/PGSQL |
Date: | 2008-04-23 06:34:02 |
Message-ID: | 65937bea0804222334l43ad8a9m23fba6a035749adc@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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