From: | "Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> |
---|---|
To: | tgl(at)sss(dot)pgh(dot)pa(dot)us |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: commit callback, request, SOLVED |
Date: | 2006-04-05 18:29:26 |
Message-ID: | BAY20-F2BEA755B3CC4A24EC77EFF9CB0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Refered triggers works well, better than I expected. It's not equal NOTIFY,
but it works.
Thank You
Pavel Stehule
CREATE OR REPLACE FUNCTION dbms_alert._defered_signal() RETURNS trigger AS
$$
BEGIN
PERFORM dbms_alert._signal(NEW.event, NEW.message);
DELETE FROM ora_alerts WHERE id=NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE;
CREATE OR REPLACE FUNCTION dbms_alert.signal(_event text, _message text)
RETURNS void AS $$
BEGIN
PERFORM 1 FROM pg_catalog.pg_class c
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relkind='r' AND c.relname = 'ora_alerts';
IF NOT FOUND THEN
CREATE TEMP TABLE ora_alerts(id serial PRIMARY KEY, event text, message
text);
REVOKE ALL ON TABLE ora_alerts FROM PUBLIC;
CREATE CONSTRAINT TRIGGER ora_alert_signal AFTER INSERT ON ora_alerts
INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE
dbms_alert._defered_signal();
END IF;
INSERT INTO ora_alerts(event, message) VALUES(_event, _message);
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
drop table test_alert cascade;
create table test_alert(v varchar);
create or replace function checkdata() returns void as $$
declare r record; d record;
begin
perform dbms_alert.register('refresh');
while true loop
select into r * from dbms_alert.waitone('refresh',100000);
perform pg_sleep(0.1); -- I need wait moment
select into d * from test_alert where v = r.message;
raise notice 'found %', d;
end loop;
end;
$$ language plpgsql;
create or replace function ins(varchar) returns void as $$
begin
insert into test_alert values($1);
perform dbms_alert.signal('refresh',$1);
end;
$$ language plpgsql;
_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2006-04-05 20:19:59 | Re: First Aggregate Funtion? |
Previous Message | Pavel Stehule | 2006-04-05 18:01:25 | request: muting notice CREATE TABLE will create implicit sequence |