plpgsql, double quoted column names containing spaces/hyphens

From: "Knut P(dot) Lehre" <k(dot)p(dot)lehre(at)tiscali(dot)no>
To: pgsql-sql(at)postgresql(dot)org
Subject: plpgsql, double quoted column names containing spaces/hyphens
Date: 2003-06-20 20:24:07
Message-ID: 5.1.0.14.2.20030620214454.00c200e0@uio-pop.uio.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Using pgsql 7.2.1 (PeerDirect beta4) on WinXP Prof SP1. Client: MS
VStudio2003 C++ -> ADO -> ODBC 7.03.01.00.

In plpgsql, I can access columns with a name containing capital letters by
enclosing the name in double quotes: tbl."ColName"
However, if the quoted name contains spaces or hyphens I get an error
message. It is surprising that this does not work in plpgsql when it does
with sql.

Thus, the following line produces an error (unterminated ") in plpgsql:
IF OLD."-person-initials" ... THEN

However, I discovered that rewriting using EXECUTE does work:
DECLARE curs1 refcursor; personinitials int4;
...
OPEN curs1 FOR EXECUTE ''SELECT "-person-initials" AS pers FROM
"''||TG_RELNAME||''" WHERE id=''||OLD.id;
FETCH curs1 INTO personinitials;
CLOSE curs1;
IF personinitials ... THEN

Could I have solved this problem in an easier way, by using some kind of
escape codes in the example producing the error (IF OLD."-person-initials"
... THEN)?

Thanks, KP

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2003-06-21 22:18:01 Re: plpgsql, double quoted column names containing spaces/hyphens
Previous Message javier garcia - CEBAS 2003-06-20 17:33:35 Re: date question