From: Jean-Luc Lachance <jllachan@sympatico.ca>
To: Theodore Petrosky <tedpet5@yahoo.com>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: postgres SQL <pgsql-sql@postgresql.org>
Subject: Re: where is this problem (trigger)
Date: Sat, 15 May 2004 10:27:24 -0400
Message-ID: <40A628CC.9000204@sympatico.ca> (raw)
In-Reply-To: <20040515134247.26712.qmail@web41006.mail.yahoo.com>
References: <20040515134247.26712.qmail@web41006.mail.yahoo.com>
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 <sszabo@megazone.bigpanda.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@postgresql.org
>
>
>
>
>
> __________________________________
> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@sympatico.ca, tedpet5@yahoo.com, sszabo@megazone.bigpanda.com
Subject: Re: where is this problem (trigger)
In-Reply-To: <40A628CC.9000204@sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox