It seems that tables in PostgreSQL must have a field id in order for the _id to be generated:
Originally, I have this table
CREATE TABLE staff
( staff_id integer NOT NULL
, name varchar(64) NOT NULL
, role smallint NOT NULL
, PRIMARY KEY (id)
);
It gave me the following errors:
I20151011-16:42:45.575(8)? at observe-driver/polling-driver.js:352:15
I20151011-16:42:45.575(8)? at Array.forEach (packages/es5-shim/.npm/package/node_modules/es5-shim/es5-shim.js:417:1)
I20151011-16:42:45.576(8)? at PgLiveQuery._updateQuery (observe-driver/polling-driver.js:349:12)
I20151011-16:42:45.576(8)? at observe-driver/polling-driver.js:447:12
I20151011-16:42:45.577(8)? at Array.forEach (packages/es5-shim/.npm/package/node_modules/es5-shim/es5-shim.js:417:1)
I20151011-16:42:45.577(8)? at PgLiveQuery._drainQueue (observe-driver/polling-driver.js:446:21)
I20151011-16:42:45.578(8)? at [object Object]._.extend.withValue (packages/meteor/dynamics_nodejs.js:56:1)
I20151011-16:42:45.578(8)? at packages/meteor/timers.js:6:1
I20151011-16:42:45.579(8)? at runWithEnvironment (packages/meteor/dynamics_nodejs.js:108:1)
I listened to the message (I changed the name) sent by the trigger due to an insert
Asynchronous notification "simple_pg_jRFT" with payload "3136e148432994dcff7955d2c24bafc0:1:1:{"table":"staff","op":"INSERT","data":[{"staff_id":95,"name":"XXXXX XXXXX XXXXX","role":17}]}" received from server process with PID 6889.
I later changed the table to
CREATE TABLE staff
( id integer NOT NULL
, name varchar(64) NOT NULL
, role smallint NOT NULL
, PRIMARY KEY (id)
);
The error disappear. The notification message for an insert became (I changed the name)
Asynchronous notification "simple_pg_Bk6v" with payload "f6841a5af85def8466d03e85a68f1665:1:1:{"table":"staff","op":"INSERT","data":[{"id":95,"name":"XXXXX XXXXX XXXXX","role":17}]}" received from server process with PID 7180.
I looked at trigger
DECLARE
row_data RECORD;
full_msg TEXT;
full_len INT;
cur_page INT;
page_count INT;
msg_hash TEXT;
BEGIN
IF (TG_OP = 'INSERT') THEN
SELECT
TG_TABLE_NAME AS table,
TG_OP AS op,
json_agg(NEW) AS data
INTO row_data;
ELSIF (TG_OP = 'DELETE') THEN
SELECT
TG_TABLE_NAME AS table,
TG_OP AS op,
json_agg(OLD) AS data
INTO row_data;
ELSIF (TG_OP = 'UPDATE') THEN
SELECT
TG_TABLE_NAME AS table,
TG_OP AS op,
json_agg(NEW) AS new_data,
json_agg(OLD) AS old_data
INTO row_data;
END IF;
SELECT row_to_json(row_data)::TEXT INTO full_msg;
SELECT char_length(full_msg) INTO full_len;
SELECT (full_len / 7950) + 1 INTO page_count;
SELECT md5(full_msg) INTO msg_hash;
FOR cur_page IN 1..page_count LOOP
PERFORM pg_notify('simple_pg_MjFR',
msg_hash || ':' || page_count || ':' || cur_page || ':' ||
substr(full_msg, ((cur_page - 1) * 7950) + 1, 7950)
);
END LOOP;
RETURN NULL;
END;
For an insert operation, the code is
IF (TG_OP = 'INSERT') THEN
SELECT
TG_TABLE_NAME AS table,
TG_OP AS op,
json_agg(NEW) AS data
INTO row_data;
It really use json_add to obtain the data, which include the field name.
This is a very limited situation as I need to change many tables. I have not tested out for tables with combination of fields as primary key, and tables without primary key at all (not unusual).
It seems that tables in PostgreSQL must have a field
idin order for the_idto be generated:Originally, I have this table
It gave me the following errors:
I listened to the message (I changed the name) sent by the trigger due to an insert
I later changed the table to
The error disappear. The notification message for an insert became (I changed the name)
I looked at trigger
For an insert operation, the code is
It really use
json_addto obtain the data, which include the field name.This is a very limited situation as I need to change many tables. I have not tested out for tables with combination of fields as primary key, and tables without primary key at all (not unusual).