summaryrefslogtreecommitdiff
path: root/init.sql
diff options
context:
space:
mode:
authorTrygve Laugstøl <trygvis@inamo.no>2014-09-10 00:12:30 +0200
committerTrygve Laugstøl <trygvis@inamo.no>2014-09-12 09:08:00 +0200
commitd6989f1e54104d09b8af6d22cf46ea4f6fc5f4dc (patch)
tree2e9cd83b8a9425ea184b72c12721d7e08ea87d52 /init.sql
downloadjavazone-2014-d6989f1e54104d09b8af6d22cf46ea4f6fc5f4dc.tar.gz
javazone-2014-d6989f1e54104d09b8af6d22cf46ea4f6fc5f4dc.tar.bz2
javazone-2014-d6989f1e54104d09b8af6d22cf46ea4f6fc5f4dc.tar.xz
javazone-2014-d6989f1e54104d09b8af6d22cf46ea4f6fc5f4dc.zip
o Initial import of postgresql LISTEN/NOTIFY code.HEADmaster
Diffstat (limited to 'init.sql')
-rw-r--r--init.sql37
1 files changed, 37 insertions, 0 deletions
diff --git a/init.sql b/init.sql
new file mode 100644
index 0000000..0ec0477
--- /dev/null
+++ b/init.sql
@@ -0,0 +1,37 @@
+-- Create a table for incoming mail
+
+CREATE TABLE mail_raw (
+ id SERIAL PRIMARY KEY,
+ raw TEXT
+);
+
+-- Insert a row into the table;
+
+INSERT INTO mail_raw (raw) VALUES ('my mail');
+NOTIFY mail_raw;
+
+--
+-- Alternate technique with implicit notifies through triggers
+--
+
+CREATE TABLE mail_raw_t (
+ id SERIAL PRIMARY KEY,
+ raw TEXT
+);
+
+-- This trigger function does the notification
+CREATE FUNCTION notify_trigger()
+ RETURNS TRIGGER AS $$
+BEGIN
+ PERFORM pg_notify(TG_TABLE_NAME, NEW.id :: TEXT);
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Create a trigger on the mail_raw_t table and make it run on each new row
+CREATE TRIGGER mail_raw_t_notify
+AFTER INSERT ON mail_raw_t
+FOR EACH ROW
+EXECUTE PROCEDURE notify_trigger();
+
+INSERT INTO mail_raw_t (raw) VALUES ('my mail');