Thread: Difficulty with quotes

Hi,

I'm trying to write a very simple SQL function, but I'm having problems
with it. My function is as follows:

CREATE OR REPLACE FUNCTION fnCPUpdateVolunteerType(int, text) RETURNS
INTEGER AS '
    UPDATE vblVolunteerType
       SET vchDisplayName = ''$2''
     WHERE intVolunteerTypeID= $1;

    SELECT 1;
' LANGUAGE SQL;

A very simple update function which gets accepted with no problems.

Then when I run something like this:

SELECT fnCPUpdateVolunteerType(17, 'Test');

The value of vchDisplayName in the applicable row gets set to '$2'.

I know I'm getting something very simple wrong but I can't figure it out
and I can't find anything in the documentation. Any help would be
greatly appreciated.

Russell Brown.

Change this line:
  SET vchDisplayName = ''$2''

To this:
  SET vchDisplayName = $2

/Mattias


----- Original Message -----
From: "Russ Brown" <postgres@dot4dot.plus.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, June 17, 2003 3:04 PM
Subject: [GENERAL] Difficulty with quotes


> Hi,
>
> I'm trying to write a very simple SQL function, but I'm having problems
> with it. My function is as follows:
>
> CREATE OR REPLACE FUNCTION fnCPUpdateVolunteerType(int, text) RETURNS
> INTEGER AS '
>     UPDATE vblVolunteerType
>        SET vchDisplayName = ''$2''
>      WHERE intVolunteerTypeID= $1;
>
>     SELECT 1;
> ' LANGUAGE SQL;
>
> A very simple update function which gets accepted with no problems.
>
> Then when I run something like this:
>
> SELECT fnCPUpdateVolunteerType(17, 'Test');
>
> The value of vchDisplayName in the applicable row gets set to '$2'.
>
> I know I'm getting something very simple wrong but I can't figure it out
> and I can't find anything in the documentation. Any help would be
> greatly appreciated.
>
> Russell Brown.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>