Thread: user-defined functions/triggers; cannot dump

user-defined functions/triggers; cannot dump

From
Jason Friedman
Date:
I recently added a table, a function, and trigger to launch that
function upon update.

drop sequence bad_email_address_id;
create sequence bad_email_address_id;
drop table bad_email_address;
create table bad_email_address (
id integer not null default nextval('bad_email_address_id'),
email_address varchar(100) not null,
error_message varchar(100),
create_date datetime default current_timestamp(0),
last_update_date datetime
);
alter table bad_email_address add constraint bad_email_address_pk
primary key (id);
alter table bad_email_address add constraint bad_email_address_u
unique(email_address,error_message);

drop function trigger_update_bad_email_addres();
drop function trigger_update_bad_email_address();
drop function update_bad_email_address();
CREATE FUNCTION "update_bad_email_address" () RETURNS opaque AS 'begin
new.last_update_date = current_timestamp(0);
return new;
end;' LANGUAGE 'plpgsql';

DROP TRIGGER "trigger_update_bad_email_addres" ON bad_email_address;
DROP TRIGGER "update_bad_email_address" ON bad_email_address;
CREATE TRIGGER "update_bad_email_address"
BEFORE UPDATE ON "bad_email_address"
FOR EACH ROW EXECUTE PROCEDURE "update_bad_email_address" ();


First, how do I list user-defined functions?

Second, why do I get this error when I dump:

pg_dump -s dmatchingmoms pg_dump: query to obtain procedure name for
trigger "trigger_update_bad_email_addres" did not return exactly one
result

Re: user-defined functions/triggers; cannot dump

From
Tom Lane
Date:
Jason Friedman <jason@powerpull.net> writes:
> Second, why do I get this error when I dump:

> pg_dump -s dmatchingmoms pg_dump: query to obtain procedure name for
> trigger "trigger_update_bad_email_addres" did not return exactly one
> result

Are you sure you dropped the trigger as well as the underlying function?
Your trace indicates some indecision about how many s's there are in
"address", so I'm suspecting it's just a typing error --- you probably
dropped the function and not the trigger.

In case it's any comfort, 7.3 should have some interlocks that will
prevent this all-too-common mistake.

            regards, tom lane