push_notifications:postgresql
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
push_notifications:postgresql [2015/02/11 09:32] – Esempio letti | push_notifications:postgresql [2015/03/03 17:33] (current) – letti | ||
---|---|---|---|
Line 70: | Line 70: | ||
< | < | ||
- | CREATE OR REPLACE FUNCTION | + | CREATE OR REPLACE FUNCTION push_notifications_login(url text, login text, password text) |
RETURNS text AS | RETURNS text AS | ||
$BODY$ | $BODY$ | ||
Line 89: | Line 89: | ||
LANGUAGE plpythonu VOLATILE | LANGUAGE plpythonu VOLATILE | ||
COST 100; | COST 100; | ||
- | ALTER FUNCTION | + | ALTER FUNCTION push_notifications_login(text, |
- | OWNER TO matteo; | + | OWNER TO postgres; |
</ | </ | ||
Line 98: | Line 98: | ||
< | < | ||
- | CREATE OR REPLACE FUNCTION | + | CREATE OR REPLACE FUNCTION push_notifications_notify(login_url text, login text, password text, push_url text, channel text, token text, payload text) |
RETURNS text AS | RETURNS text AS | ||
$BODY$ | $BODY$ | ||
Line 108: | Line 108: | ||
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj)) | opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj)) | ||
- | notify_values | + | login_values |
- | login_data = urllib.urlencode(notify_values) | + | login_data = urllib.urlencode(login_values) |
- | notify_resp | + | login_resp |
- | return | + | |
+ | if login_resp.getcode() == ' | ||
+ | |||
+ | notify_values = {' | ||
+ | notify_data = urllib.urlencode(notify_values) | ||
+ | |||
+ | | ||
+ | |||
+ | if notify_restp.getcode() | ||
+ | return True | ||
+ | else: | ||
+ | return False | ||
$BODY$ | $BODY$ | ||
LANGUAGE plpythonu VOLATILE | LANGUAGE plpythonu VOLATILE | ||
COST 100; | COST 100; | ||
- | ALTER FUNCTION | + | ALTER FUNCTION push_notifications_notify(text, text, text, text, text, text, text) |
- | OWNER TO matteo; | + | OWNER TO postgres; |
</ | </ | ||
Line 148: | Line 159: | ||
EXECUTE format(' | EXECUTE format(' | ||
EXECUTE format(' | EXECUTE format(' | ||
- | payload := ' | + | payload := ' |
login_url := ' | login_url := ' | ||
notify_url := ' | notify_url := ' | ||
- | | + | |
) USING login_url; | ) USING login_url; | ||
| | ||
| | ||
- | EXECUTE format(' | + | EXECUTE format(' |
- | ) USING notify_url, | + | ) USING login_url, login, password, |
| | ||
END; | END; | ||
Line 168: | Line 179: | ||
==== 4. agganciare trigger al server ==== | ==== 4. agganciare trigger al server ==== | ||
- | ==== 5. ESEMPIO | + | ==== 5. ESEMPIO |
+ | |||
+ | Target database: | ||
+ | |||
+ | < | ||
+ | Lista delle relazioni | ||
+ | | ||
+ | --------+---------------------------------+----------+-------------- | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | </ | ||
+ | |||
+ | < | ||
+ | |||
+ | CREATE TABLE " | ||
+ | CREATE TABLE " | ||
+ | CREATE TABLE " | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | Define functions as in section 2 and 3. | ||
+ | |||
+ | < | ||
+ | |||
+ | INSERT INTO honeywell_heatpump (heat, | ||
+ | INSERT INTO honeywell_channel (channel) VALUES (' | ||
+ | INSERT INTO honeywell_heatpump_token (heat_pump_id, | ||
+ | |||
+ | </ | ||
+ | |||
+ | < | ||
+ | |||
+ | UPDATE honeywell_heatpump SET status = 1; | ||
+ | |||
+ | </ | ||
+ | |||
+ | ==== 5.1 ESEMPIO 2 ==== | ||
+ | |||
+ | Esempio di trigger su una tabella di uno schema. | ||
+ | |||
+ | === Creazione funzione da triggherare === | ||
+ | |||
+ | < | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION notify_status_update() RETURNS trigger AS $notify_status_update$ | ||
+ | DECLARE | ||
+ | |||
+ | app_key text := ' | ||
+ | channel text := TG_TABLE_SCHEMA; | ||
+ | token text := ' | ||
+ | payload text; | ||
+ | login_url text := ' | ||
+ | notify_url text := ' | ||
+ | |||
+ | OLD RECORD; | ||
+ | TABLE_FULLNAME text := '"' | ||
+ | board_label text; | ||
+ | |||
+ | BEGIN | ||
+ | |||
+ | |||
+ | RAISE NOTICE ' | ||
+ | SELECT label FROM acs_service.chamber WHERE dbname = TG_TABLE_SCHEMA INTO board_label; | ||
+ | SELECT * FROM get_last_reliable_var_row(TABLE_FULLNAME, | ||
+ | |||
+ | id integer, | ||
+ | variablename character varying, | ||
+ | val character varying, | ||
+ | statuscode character varying, | ||
+ | " | ||
+ | timestampe integer, | ||
+ | started integer, | ||
+ | sampletime integer, | ||
+ | idactor integer | ||
+ | ) INTO OLD; | ||
+ | |||
+ | RAISE NOTICE ' | ||
+ | -- Do nothing if only statuscode is changed... | ||
+ | IF OLD.val != NEW.val THEN | ||
+ | |||
+ | IF NEW.val:: | ||
+ | payload := board_label || ' is faulting: error ' || NEW.val; | ||
+ | ELSE | ||
+ | payload := board_label || ' no faulting anymore'; | ||
+ | END IF; | ||
+ | |||
+ | RAISE NOTICE ' | ||
+ | EXECUTE format(' | ||
+ | ) USING login_url; | ||
+ | |||
+ | |||
+ | EXECUTE format(' | ||
+ | ) USING notify_url, channel, token, payload; | ||
+ | RAISE NOTICE ' | ||
+ | |||
+ | END IF; | ||
+ | |||
+ | RETURN NEW; | ||
+ | END; | ||
+ | $notify_status_update$ LANGUAGE plpgsql; | ||
+ | |||
+ | </ | ||
+ | |||
+ | === Creazione del trigger (DROP preventivo se necessario) === | ||
+ | |||
+ | La CREATE è fatta su un AFTER INSERT perchè ad un update su una righa corrisponde, | ||
+ | |||
+ | < | ||
+ | |||
+ | drop trigger notify_status_update ON [RELATION]; | ||
+ | CREATE TRIGGER notify_status_update AFTER INSERT ON [RELATION] | ||
+ | FOR EACH ROW WHEN ( NEW.statuscode NOT LIKE ' | ||
+ | |||
+ | </ | ||
+ | |||
+ | Ad esempio: " | ||
+ | |||
+ | |||
+ | ==== GET LAST ROW BEFORE INSERT ==== | ||
+ | |||
+ | Funzione per prendere l' | ||
+ | |||
+ | Dato che siamo in una AFTER INSERT, l' | ||
+ | |||
+ | Per questo, dalla select viene esclusa l' | ||
+ | |||
+ | < | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION get_last_reliable_var_row(tablename text, id integer) | ||
+ | RETURNS RECORD AS | ||
+ | $BODY$ | ||
+ | DECLARE | ||
+ | last_row RECORD; | ||
+ | BEGIN | ||
+ | EXECUTE ' | ||
+ | || quote_literal(' | ||
+ | INTO last_row; | ||
+ | RETURN last_row; | ||
+ | END; | ||
+ | $BODY$ | ||
+ | COST 100; | ||
+ | |||
+ | </ | ||
push_notifications/postgresql.1423647179.txt.gz · Last modified: 2015/02/11 09:32 by letti