Re: commit callback, request, SOLVED

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/