Re: Quoted identifiers in queries in plpgsql functions. - Mailing list pgsql-general

To maintain some compatibility with some non-Postgresql applications,
we thought it would be useful to retain the spaces in the column
names.  And in fact, that works just fine in normal queries,
but not from within function bodies.  Its just that functions
are inconsistent in that respect, and I haven't yet found
a way to keep the blanks in the names.  Its almost as if the
tokenizer, or something, is handling the quotes differently, so
in that respect, you are correct -- the problems are with the
blanks and not the quotes.

Had I known there was a problem from the start of the project,
I may not have kept the blanks in the names, but now I would
prefer not to reverse that decision, if I don't have to.

nh

On Thu, Jun 20, 2002 at 01:42:37AM +0900, Masaru Sugawara wrote:
> On Wed, 19 Jun 2002 08:46:21 -0400
> Neal Holtz <nholtz@docuweb.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@docuweb.ca
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
>
> Regards,
> Masaru Sugawara
>
>

--
Neal Holtz                             http://www.docuweb.ca/~nholtz
Dept. of Civil and Environmental Engineering,   Carleton University,
Ottawa, Ontario, Canada K1S 5B6.                   nholtz@docuweb.ca