我的程式筆記本: 6月 2016

Postgresql 有個在做即時互動很好用的功能叫Notify.

根據Documentation表示, Notify 可以在table 有insert/update/delete 的時候推出一個通知.
那麼來做個小測試吧~
首先, 我們用 pgadmin 的 query 視窗先建立一個測試用的table

CREATE TABLE test_table

(

id serial NOT NULL,

test_col_1 character varying

)

然後, 建立一個 trigger 觸發後要執行的 function

	CREATE OR REPLACE FUNCTION notify_event() RETURNS TRIGGER AS $$
	BEGIN
	// Execute pg_notify(channel, notification)
	PERFORM pg_notify('table_changed', 'new row added'::text);
	// Result is ignored since this is an AFTER trigger
	RETURN NULL;
	END;
	$$ LANGUAGE plpgsql;

view raw postgresql.js hosted with ❤ by GitHub

最後, 建立一個 trigger 來觸發事件

	//Trigger: table_changed on test_table
	CREATE TRIGGER table_changed
	AFTER INSERT
	ON test_table
	FOR EACH ROW
	EXECUTE PROCEDURE notify_event();

view raw trigger.js hosted with ❤ by GitHub

DB 的部分處理好了之後, 我們來寫一段小程式試試看

	<?php
	include '../conn.php';
	set_time_limit(0);
	ob_end_clean();
	//開始監聽之前要先向DB下一個 LISTEN table_changed 的 query
	pg_query($conn, 'LISTEN table_changed;');

	while(true){
	$notify = pg_get_notify($conn);
	if (!$notify) {
	echo json_encode(array('result'=>false, 'data'=>'No messages')).PHP_EOL;
	ob_flush();
	flush();
	sleep(1);
	} else {
	echo json_encode(array('result'=>true, 'process_id'=>$pid , 'pid' => pg_get_pid($conn), 'data' => $notify)).PHP_EOL;

	}


	}