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,
eventtext, 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
wherev = 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/