pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect and functions
3+ messages / 3 participants
[nested] [flat]
* Select and functions
@ 2003-11-05 14:26 Stephen J. Thompson <stephen@cass-ltd.co.uk>
2003-11-05 14:38 ` Re: Select and functions terry@ashtonwoodshomes.com
2003-11-05 19:54 ` Re: Select and functions Jeff Eckermann <jeff_eckermann@yahoo.com>
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:26 Select and functions Stephen J. Thompson <stephen@cass-ltd.co.uk>
@ 2003-11-05 14:38 ` terry@ashtonwoodshomes.com
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 14:26 Select and functions Stephen J. Thompson <stephen@cass-ltd.co.uk>
@ 2003-11-05 19:54 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
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