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
[EMAIL PROTECTED]
Fax: (416) 441-9085
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Stephen
> J. Thompson
> Sent: Wednesday, November 05, 2003 9:26 AM
> To: [EMAIL PROTECTED]
> 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 [EMAIL PROTECTED] so
> that your
> message can get through to the mailing list cleanly
>
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
