Thread: 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-----

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
> 

--- "Stephen J. Thompson" <stephen@cass-ltd.co.uk>
wrote:

> 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?

Single quotes inside function bodies need to be
escaped.  The easiest way is to double them, i.e. from
one single quote to two single quotes.  Backslashes
would work just as well (and also need to be escaped
if used in a function body).

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree