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/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
