Thread: Trigger problem, record "new" is not assigned yet
Hello.
I'm probably doing some very basic error here, but I get
ERROR: record "new" is not assigned yet
The tuple structure of a not-yet-assigned record is indeterminate.
when I try this small example
create table foo(x int);
create or replace function trigger_foo() returns trigger language plpgsql as $$
declare v_i integer;
begin
select count(1) into v_i from foo;
if new.x >18 then
raise exception 'error';
else
return null;
end if;
end; $$;
CREATE TRIGGER trigger_foo AFTER INSERT ON foo for each row EXECUTE
PROCEDURE trigger_foo();
insert into foo (x) values (15);
And the table foo only have 14 lines right now.
Can someone help me spot the error? :-)
On Thu, Feb 10, 2011 at 9:29 AM, A B <gentosaker@gmail.com> wrote: > Can someone help me spot the error? :-) > use a BEFORE INSERT trigger?
Thanks for the suggestion, but CREATE TRIGGER trigger_foo BEFORE INSERT ON foo for each row EXECUTE PROCEDURE trigger_foo(); gives me the same error. 2011/2/10 Vick Khera <vivek@khera.org>: > On Thu, Feb 10, 2011 at 9:29 AM, A B <gentosaker@gmail.com> wrote: >> Can someone help me spot the error? :-) >> > > use a BEFORE INSERT trigger? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Thursday, February 10, 2011 6:29:58 am A B wrote: > Hello. > > I'm probably doing some very basic error here, but I get > > ERROR: record "new" is not assigned yet > The tuple structure of a not-yet-assigned record is indeterminate. > > when I try this small example > > create table foo(x int); > > create or replace function trigger_foo() returns trigger language plpgsql > as $$ declare v_i integer; > begin > select count(1) into v_i from foo; > if new.x >18 then > raise exception 'error'; > else > return null; > end if; > end; $$; > > CREATE TRIGGER trigger_foo AFTER INSERT ON foo for each row EXECUTE > PROCEDURE trigger_foo(); > > insert into foo (x) values (15); > > > And the table foo only have 14 lines right now. > > Can someone help me spot the error? :-) The above works here, Postgres 9.0.3. Was that the complete error message? Is there more than one foo across the schemas? -- Adrian Klaver adrian.klaver@gmail.com
On Thu, Feb 10, 2011 at 9:38 AM, A B <gentosaker@gmail.com> wrote:
> CREATE TRIGGER trigger_foo BEFORE INSERT ON foo for each row EXECUTE
> PROCEDURE trigger_foo();
>
> gives me the same error.
>
Maybe "NEW" needs to be all caps? Also, with the BEFORE trigger,
you'll need to RETURN NEW.
This trigger of mine works just fine as a before trigger, FWIW:
CREATE OR REPLACE FUNCTION sessions_update_lastuse() RETURNS TRIGGER
AS $$
BEGIN
NEW.lastuse = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
I'm very embarresed now. There were another trigger that caused a problem. Now it works. Thank you all for helping! :-) 2011/2/10 Adrian Klaver <adrian.klaver@gmail.com>: > On Thursday, February 10, 2011 6:29:58 am A B wrote: >> Hello. >> >> I'm probably doing some very basic error here, but I get >> >> ERROR: record "new" is not assigned yet >> The tuple structure of a not-yet-assigned record is indeterminate. >> >> when I try this small example >> >> create table foo(x int); >> >> create or replace function trigger_foo() returns trigger language plpgsql >> as $$ declare v_i integer; >> begin >> select count(1) into v_i from foo; >> if new.x >18 then >> raise exception 'error'; >> else >> return null; >> end if; >> end; $$; >> >> CREATE TRIGGER trigger_foo AFTER INSERT ON foo for each row EXECUTE >> PROCEDURE trigger_foo(); >> >> insert into foo (x) values (15); >> >> >> And the table foo only have 14 lines right now. >> >> Can someone help me spot the error? :-) > > The above works here, Postgres 9.0.3. Was that the complete error message? Is > there more than one foo across the schemas? > > > -- > Adrian Klaver > adrian.klaver@gmail.com >