From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Alexander Reichstadt *EXTERN*" <info(at)apfeltaste(dot)net>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Question on notifications |
Date: | 2012-04-30 12:58:58 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C207D4F77B@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Alexander Reichstadt wrote:
> Thanks, I had checked the example before but couldn't make sense out
of it in terms of wrapping it in
> Objective-C. I left it in C now and it works fine.
>
> The trigger I am using now looks like this:
>
> CREATE FUNCTION notify_trigger() RETURNS trigger AS $$
>
>
> DECLARE
>
>
> BEGIN
> IF ( TG_OP = 'INSERT' ) THEN
> execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || NEW.oid;
> ELSE
> execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || OLD.oid;
> END IF;
> return NULL;
> END;
>
>
> $$ LANGUAGE plpgsql;
>
> it works if I remove transmission of the OID. If I do transmit the
OID I get
>
>
> DETAIL: The tuple structure of a not-yet-assigned record is
indeterminate.
> CONTEXT: PL/pgSQL function "notify_trigger" line 1 at EXECUTE
statement
>
>
> The trigger is firing above function AFTER the TG_OP took place. The
error is thrown upon insertion.
> Doesn't the record exist given I trigger AFTER and not BEFORE the
operation took palce?
Right, and I cannot reproduce the error you get.
Here's what I do (on PostgreSQL 9.1.3):
CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
EXECUTE 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', '''
|| CASE WHEN TG_OP = 'INSERT' THEN NEW.oid ELSE OLD.oid END ||
'''';
RETURN NEW;
END$$;
Observe that the second argument to NOTIFY is a string.
CREATE TABLE t1(val text, PRIMARY KEY (oid)) WITH OIDS;
CREATE TRIGGER t1_trig AFTER INSERT OR UPDATE OR DELETE ON t1
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();
INSERT INTO t1 (val) VALUES ('test');
UPDATE t1 SET val=NULL;
DELETE FROM t1;
A second session subscribed to the events gets:
Asynchronous notification "t1_insert" with payload "46728" received from
server process with PID 18687.
Asynchronous notification "t1_update" with payload "46728" received from
server process with PID 18687.
Asynchronous notification "t1_delete" with payload "46728" received from
server process with PID 18687.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Reichstadt | 2012-04-30 13:48:45 | Re: Question on notifications |
Previous Message | Alexander Reichstadt | 2012-04-30 12:41:45 | Re: QUestion on notifications |