From: <terry@ashtonwoodshomes.com>
To: <stephen@cass-ltd.co.uk>, <pgsql-sql@postgresql.org>
Subject: Re: Select and functions
Date: Wed, 5 Nov 2003 09:38:11 -0500
Message-ID: <039d01c3a3aa$70984120$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In-Reply-To: <200311051426.19315.stephen@cass-ltd.co.uk>
Importance: Normal

You need to use 2 single quotes:
CREATE OR REPLACE FUNCTION staging.write_work_country()
  RETURNS trigger AS
'
	DECLARE
		alias_rec RECORD;
	BEGIN
		-- Clean the phone number
		NEW.worktelephonenumber = 
REPLACE(NEW.worktelephonenumber,'' '','''');
		RETURN NEW;
	END;
' 

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com 
Fax: (416) 441-9085


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephen 
> J. Thompson
> Sent: Wednesday, November 05, 2003 9:26 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Select and functions
> 
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hello all,
> 
> I am having a little bit of trouble here. 
> 
> If I use the replace function in a select statement all works 
> as expected.
> 
> select firstname, lastname, replace(worktelephonenumber, ' ', 
> '') AS worktel 
> from contacts;
> 
> But if I use it in a before trigger the procedure fails to work. 
> 
> CREATE OR REPLACE FUNCTION staging.write_work_country()
>   RETURNS trigger AS
> '
> 	DECLARE
> 		alias_rec RECORD;
> 	BEGIN
> 		-- Clean the phone number
> 		NEW.worktelephonenumber = 
> REPLACE(NEW.worktelephonenumber,' ','');
> 		RETURN NEW;
> 	END;
> ' 
> 
> I get the following error:
> 
> ERROR:  parser: parse error at or near "','');
> 
> Can anyone help me please?
> 
> Thanks.
> 
> Stephen.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.3 (GNU/Linux)
> 
> iD8DBQE/qQiKDAwPLUWQb6sRAhNpAJ0QdVzmYdhAhFVXf06v5zACuS3y9wCfSSlS
> W18AFuYb4J1le0W32uuIJto=
> =xe/b
> -----END PGP SIGNATURE-----
> 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so 
> that your
>       message can get through to the mailing list cleanly
>