X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id D1D7CD1B190
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 15 May 2004 11:29:18 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 62816-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 15 May 2004 11:29:02 -0300 (ADT)
Received: from web41009.mail.yahoo.com (web41009.mail.yahoo.com [66.218.93.8])
	by svr1.postgresql.org (Postfix) with SMTP id E2C09D1B196
	for <pgsql-sql@postgresql.org>; Sat, 15 May 2004 11:28:18 -0300 (ADT)
Message-ID: <20040515142821.90540.qmail@web41009.mail.yahoo.com>
Received: from [66.108.25.20] by web41009.mail.yahoo.com via HTTP;
	Sat, 15 May 2004 07:28:21 PDT
Date: Sat, 15 May 2004 07:28:21 -0700 (PDT)
From: Theodore Petrosky <tedpet5@yahoo.com>
Subject: Re: where is this problem (trigger)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: postgres SQL <pgsql-sql@postgresql.org>
In-Reply-To: <20040515134247.26712.qmail@web41006.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS
X-Spam-Level: 
X-Archive-Number: 200405/138
X-Sequence-Number: 17852

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/