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(at)greatgulfhomes(dot)com
Fax: (416) 441-9085

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Stephen
> J. Thompson
> Sent: Wednesday, November 05, 2003 9:26 AM
> To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org so
> that your
> message can get through to the mailing list cleanly
>