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 7E2E1D1B193
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 15 May 2004 10:46:23 -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 49362-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 15 May 2004 10:46:00 -0300 (ADT)
Received: from web41006.mail.yahoo.com (web41006.mail.yahoo.com [66.218.93.5])
	by svr1.postgresql.org (Postfix) with SMTP id 5446DD1B191
	for <pgsql-sql@postgresql.org>; Sat, 15 May 2004 10:43:03 -0300 (ADT)
Message-ID: <20040515134247.26712.qmail@web41006.mail.yahoo.com>
Received: from [66.108.25.20] by web41006.mail.yahoo.com via HTTP;
	Sat, 15 May 2004 06:42:47 PDT
Date: Sat, 15 May 2004 06:42:47 -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: <20040514080501.M35179@megazone.bigpanda.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/136
X-Sequence-Number: 17850

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/