pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Select and functions
3+ messages / 3 participants
[nested] [flat]

* Select and functions
@ 2003-11-05 14:26  Stephen J. Thompson <stephen@cass-ltd.co.uk>
  0 siblings, 2 replies; 3+ messages in thread

From: Stephen J. Thompson @ 2003-11-05 14:26 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Select and functions
@ 2003-11-05 14:38  terry@ashtonwoodshomes.com
  parent: Stephen J. Thompson <stephen@cass-ltd.co.uk>
  1 sibling, 0 replies; 3+ messages in thread

From: terry@ashtonwoodshomes.com @ 2003-11-05 14:38 UTC (permalink / raw)
  To: stephen@cass-ltd.co.uk; pgsql-sql

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
> 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Select and functions
@ 2003-11-05 19:54  Jeff Eckermann <jeff_eckermann@yahoo.com>
  parent: Stephen J. Thompson <stephen@cass-ltd.co.uk>
  1 sibling, 0 replies; 3+ messages in thread

From: Jeff Eckermann @ 2003-11-05 19:54 UTC (permalink / raw)
  To: stephen@cass-ltd.co.uk; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-11-05 19:54 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-05 14:26 Select and functions Stephen J. Thompson <stephen@cass-ltd.co.uk>
2003-11-05 14:38 ` terry@ashtonwoodshomes.com
2003-11-05 19:54 ` Jeff Eckermann <jeff_eckermann@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox