From: terry@ashtonwoodshomes.com
To: stephen@cass-ltd.co.uk
To: pgsql-sql@postgresql.org
Subject: Re: Select and functions
Date: Wed, 5 Nov 2003 09:38:11 -0500
Message-ID: <039d01c3a3aa$70984120$2766f30a@development.greatgulfhomes.com> (raw)
In-Reply-To: <200311051426.19315.stephen@cass-ltd.co.uk>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: terry@ashtonwoodshomes.com, stephen@cass-ltd.co.uk
Subject: Re: Select and functions
In-Reply-To: <039d01c3a3aa$70984120$2766f30a@development.greatgulfhomes.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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