Re: where is this problem (trigger)

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 <sszabo(at)megazone(dot)bigpanda(dot)com>
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 majordomo(at)postgresql(dot)org

__________________________________
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/