Have noticed it is not the same function...
Theodore Petrosky wrote:
Great I got the double quotes in the trigger... like this:
CREATE FUNCTION notify_jobinfo() RETURNS "trigger" AS ' BEGIN EXECUTE ''NOTIFY "''||TG_RELNAME||''_''||NEW.jobnumber||''"''; RETURN NEW; END ' LANGUAGE plpgsql;
and it works great... however, i did a pg_dump of the db to back it up. On a lark I started looking through the file and I decided to look at the dumped trigger and this is what I see.
-- -- TOC entry 29 (OID 17180) -- Name: notify_on_update(); Type: FUNCTION; Schema: public; Owner: postgres --
CREATE FUNCTION notify_on_update() RETURNS "trigger"
AS ' BEGIN
EXECUTE ''NOTIFY ''||TG_RELNAME||''_''||NEW.jobnumseq;
RETURN NEW;
END
'
LANGUAGE plpgsql;
Ouch... it looks like pg_dump forgot my double quotes.
Do you have any ideas? If I reimport this dump file the triggers won't work when it meets a row with a dot in the column. Of course I can just update my data to use the underscore instead of the dot.....
Ted
--- Stephan Szabo <[EMAIL PROTECTED]> wrote:
On Wed, 12 May 2004, Theodore Petrosky wrote:
I can not seem to update these rows because of the
dot
in the jobnumber field. I have found that I can
change
the dot to an underscore but I thought I would ask
if
there is a better solution.
here is the error:
UPDATE jobinfo SET isbilled = false WHERE
jobnumber =
'1162.01'; ERROR: syntax error at or near ".01" at character
20
CONTEXT: PL/pgSQL function "notify_jobinfo" line
2 at
execute statement
From the docs, it looks like NOTIFY takes an identifier as a name. Foo_1023.01 is not a valid identifier so you might want to double quote the string since "Foo_1023.01" is one.
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
__________________________________ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
