From: Theodore Petrosky <tedpet5@yahoo.com>
To: postgres SQL <pgsql-sql@postgresql.org>
Subject: where is this problem (trigger)
Date: Wed, 12 May 2004 10:57:50 -0700 (PDT)
Message-ID: <20040512175750.12624.qmail@web41006.mail.yahoo.com> (raw)
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
__________________________________
Do you Yahoo!?
Yahoo! Movies - Buy advance tickets for 'Shrek 2'
http://movies.yahoo.com/showtimes/movie?mid=1808405861
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: tedpet5@yahoo.com
Subject: Re: where is this problem (trigger)
In-Reply-To: <20040512175750.12624.qmail@web41006.mail.yahoo.com>
* 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