Re: Quoted identifiers in queries in plpgsql functions.

On Wed, 19 Jun 2002 08:46:21 -0400
Neal Holtz <nholtz(at)docuweb(dot)ca> wrote:

I would think there is no problem of the singlequote/doublequote
in your function. This syntax error depends that plpgsql is unable to
regard the space as the part of the column name. Generally, the way
to use some space within column names is bad -- at least, is not good.
If you can recreate the tables, the 100-percent certain way is to use
underlines instead of spaces.

> How do I specify quoted identifiers in plpgsql functions?
>
> If the following is in test.sql:
>
> -- begin test.sql
> CREATE TABLE Control (
> "Full Name" varchar(255) NOT NULL PRIMARY KEY,
> "Telephone Number" varchar(255)
> );
>
> INSERT INTO Control ("Full Name","Telephone Number") VALUES
> ('neal','730-7888');
>
> CREATE OR REPLACE FUNCTION test() RETURNS varchar(255) AS '
> DECLARE
> val varchar(255);
> BEGIN
> SELECT INTO val "Telephone Number" FROM Control WHERE "Full
> Name"=''neal'';
> RETURN val;
> END;
> ' LANGUAGE 'plpgsql';
> -- end test.sql
>
> I get syntax errors when I try to execute the function:
>
> BaseContacts_Devel=# \i test.sql
> . . .
> BaseContacts_Devel=# select test();
> NOTICE: plpgsql: ERROR during compile of test near line 4
> ERROR: unterminated " in name "Telephone
>
> I've tried as many combinations of escaping as I can think of,
> but nothing has worked for me so far.
>
> I'll be grateful for any help.
>
> thanks
> neal
>
>
> --
> Neal Holtz http://www.docuweb.ca/~nholtz
> Dept. of Civil and Environmental Engineering, Carleton University,
> Ottawa, Ontario, Canada K1S 5B6. nholtz(at)docuweb(dot)ca
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Regards,
Masaru Sugawara