Re: Select and functions

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
>