Thread: where is this problem (trigger)

where is this problem (trigger)

From
Theodore Petrosky
Date:
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 


Re: where is this problem (trigger)

From
Christoph Haller
Date:
> 
> 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 



Re: where is this problem (trigger)

From
Stephan Szabo
Date:
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.



view running query

From
"Eric Anderson Vianet SAO"
Date:
    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



Re: where is this problem (trigger)

From
Theodore Petrosky
Date:
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/


Re: where is this problem (trigger)

From
Jean-Luc Lachance
Date:
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
> 



Re: where is this problem (trigger)

From
Theodore Petrosky
Date:
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/


Re: view running query

From
Andrew Hammond
Date:
-----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-----