Thread: where is this problem (trigger)
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,
holdnumbertext, insertioninfo text, jobtitle text, insertiondate text, iscomplete boolean DEFAULT false,
isbilledboolean 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
>
> 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
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.
I run an application which connects to my pgsql DB. How could I see which query is sent to DB when, an example, i push some application button (such ´find´). sds Eric Anderson CPD Via Net SAO 11-66432800
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/
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 >
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/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 There are two ways to do it. The server-side approach is to increase logging levels in the config file and then "pg_ctl reload". See http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING for the stuff involved. The other way to do it is client side. In this case, you have to increase the logging level of your database connection. For example, if you have a perl DBD application, find the initilization of the database handle (by convention named $dbh) and then add $dbh->trace(2); After it. Drew Eric Anderson Vianet SAO wrote: | I run an application which connects to my pgsql DB. | | How could I see which query is sent to DB when, an example, i push some | application button (such ´find´). | | sds | | Eric Anderson | CPD Via Net SAO | 11-66432800 | | | ---------------------------(end of broadcast)--------------------------- | TIP 4: Don't 'kill -9' the postmaster -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAqMzogfzn5SevSpoRAnDwAJ4+y6xBwD9hXQ2k7V4mJbUf26rKLQCeP74Q HdgdLOV8bpqh5z4hgcUX52M= =dVN9 -----END PGP SIGNATURE-----