From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200405141444.QAA20955@rodos>
Subject: Re: where is this problem (trigger)
To: tedpet5@yahoo.com (Theodore Petrosky)
Date: Fri, 14 May 2004 16:44:39 METDST
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20040512175750.12624.qmail@web41006.mail.yahoo.com>;
	from "Theodore Petrosky" at May 12, 104 8:16 pm

> 
> I fire this trigger whenever my client updates a row
> in the db:
> 
> CREATE FUNCTION notify_jobinfo() RETURNS "trigger"
>     AS '
> BEGIN
> EXECUTE ''NOTIFY ''||TG_RELNAME||''_''||NEW.jobnumber;
> RETURN NEW;
> END
> '
>     LANGUAGE plpgsql;
> 
> CREATE TRIGGER notify_jobinfo
>     AFTER UPDATE ON jobinfo
>     FOR EACH ROW
>     EXECUTE PROCEDURE notify_jobinfo();
> 
> 
> CREATE TABLE jobinfo (
>     acode text,
>     jobnumber text DEFAULT
> nextval('public.jobinfo_seq'::text),
>     creationdate date DEFAULT now(),
>     shortdescription text,
>     projectcode text,
>     holdnumber text,
>     insertioninfo text,
>     jobtitle text,
>     insertiondate text,
>     iscomplete boolean DEFAULT false,
>     isbilled boolean DEFAULT false,
>     CONSTRAINT "$1" CHECK ((jobnumber <> ''::text))
> );
> 
> My problem is that I have had to import legacy data
> for the jobnumber column. My client was originally
> using an excel file.  they were free to do as they
> pleased and there are jobnumbers like '1041.01'
> '1041.02'. 
> 
> 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
> 
> Ted
> 
> 
I can hardly believe the dot in '1162.01' is causing the error. 
There must be some other reason. Without showing the trigger 
procedure's code it's impossible to tell what's really wrong. 

Regards, Christoph