From: | "PEDRO LOPEZ" <p(dot)lopez(at)bresnan(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | function return type for triggers in postgresql 8.0 rc2 |
Date: | 2004-12-28 06:21:56 |
Message-ID: | web-1898811@be-2.cluster1.bresnan.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I am porting an existing database to postgresql and am translating some
triggers.
I have the following objects:
A table named 'currentstatus'
a function which I orignally defined as:
CREATE OR REPLACE FUNCTION "CurrentStatus_DTrigx"() RETURNS opaque AS
$BODY$
Begin
if (select COUNT(*) FROM deleted, Stations WHERE deleted.CurrentStatusCode =
Stations.CurrentStatusCode) > 0 then
RAISE EXCEPTION 'RAISERROR(778501, 16, 1)';
ROLLBACK TRANSACTION;
end if;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE;
a trigger I defined as:
create trigger csd before delete on currentstatus for each row
execute procedure public.CurrentStatus_DTrig()
after struggling with 'function not defined errors', and unable to see what
was wrong I redefined the function as:
CREATE OR REPLACE FUNCTION currentstatus_dtrig() RETURNS opaque AS
$BODY$
Begin
if (select COUNT(*) FROM deleted, Stations WHERE deleted.CurrentStatusCode =
Stations.CurrentStatusCode) > 0 then
RAISE EXCEPTION 'RAISERROR(778501, 16, 1)';
ROLLBACK TRANSACTION;
end if;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE;
and got the message to the effect of 'redefining return type as trigger', and
the following function was created:
-- Function: currentstatus_dtrig()
-- DROP FUNCTION currentstatus_dtrig();
CREATE OR REPLACE FUNCTION currentstatus_dtrig()
RETURNS "trigger" AS
$BODY$
Begin
if (select COUNT(*) FROM deleted, Stations WHERE deleted.CurrentStatusCode =
Stations.CurrentStatusCode) > 0 then
RAISE EXCEPTION 'RAISERROR(778501, 16, 1)';
ROLLBACK TRANSACTION;
end if;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION currentstatus_dtrig() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION currentstatus_dtrig() TO public;
GRANT EXECUTE ON FUNCTION currentstatus_dtrig() TO postgres;
This is strange, AFAICT I followed the documentation setting up the trigger
and functions (correct parameter signature and return type). Did I do
something wrong or is this a RC2 issue?
Thanks,
Pedro Lopez
From | Date | Subject | |
---|---|---|---|
Next Message | Sankaranarayanan K V | 2004-12-28 12:49:26 | Table name length: differences b/w 7.2.2 and 8.0.0-RC2 ? |
Previous Message | Tom Lane | 2004-12-27 20:41:01 | Re: config option --with-pgport does not update postgresql.conf |