pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: where is this problem (trigger)
6+ messages / 4 participants
[nested] [flat]
* where is this problem (trigger)
@ 2004-05-12 17:57 Theodore Petrosky <tedpet5@yahoo.com>
1970-01-01 00:00 ` Re: where is this problem (trigger) Christoph Haller <ch@rodos.fzk.de>
2004-05-14 15:06 ` Re: where is this problem (trigger) Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 6+ messages in thread
From: Theodore Petrosky @ 2004-05-12 17:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where is this problem (trigger)
2004-05-12 17:57 where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
@ 1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
1 sibling, 0 replies; 6+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: tedpet5@yahoo.com; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where is this problem (trigger)
2004-05-12 17:57 where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
@ 2004-05-14 15:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-05-15 13:42 ` Re: where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2004-05-14 15:06 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where is this problem (trigger)
2004-05-12 17:57 where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
2004-05-14 15:06 ` Re: where is this problem (trigger) Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-05-15 13:42 ` Theodore Petrosky <tedpet5@yahoo.com>
2004-05-15 14:27 ` Re: where is this problem (trigger) Jean-Luc Lachance <jllachan@sympatico.ca>
2004-05-15 14:28 ` Re: where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
0 siblings, 2 replies; 6+ messages in thread
From: Theodore Petrosky @ 2004-05-15 13:42 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where is this problem (trigger)
2004-05-12 17:57 where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
2004-05-14 15:06 ` Re: where is this problem (trigger) Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-05-15 13:42 ` Re: where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
@ 2004-05-15 14:27 ` Jean-Luc Lachance <jllachan@sympatico.ca>
1 sibling, 0 replies; 6+ messages in thread
From: Jean-Luc Lachance @ 2004-05-15 14:27 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where is this problem (trigger)
2004-05-12 17:57 where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
2004-05-14 15:06 ` Re: where is this problem (trigger) Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-05-15 13:42 ` Re: where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
@ 2004-05-15 14:28 ` Theodore Petrosky <tedpet5@yahoo.com>
1 sibling, 0 replies; 6+ messages in thread
From: Theodore Petrosky @ 2004-05-15 14:28 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
Ignore that last post.... of course I have a
production machine and a development machine and I
should have updated the development machine before
opening my mouth...
Everything works just fine.... thanks.
Ted
--- Theodore Petrosky <tedpet5@yahoo.com> 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.
__________________________________
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-05-15 14:28 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-12 17:57 where is this problem (trigger) Theodore Petrosky <tedpet5@yahoo.com>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2004-05-14 15:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-05-15 13:42 ` Theodore Petrosky <tedpet5@yahoo.com>
2004-05-15 14:27 ` Jean-Luc Lachance <jllachan@sympatico.ca>
2004-05-15 14:28 ` Theodore Petrosky <tedpet5@yahoo.com>
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