pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
plpgsql, double quoted column names containing spaces/hyphens
2+ messages / 2 participants
[nested] [flat]

* plpgsql, double quoted column names containing spaces/hyphens
@ 2003-06-20 20:24 Knut P. Lehre <k.p.lehre@tiscali.no>
  2003-06-21 22:18 ` Re: plpgsql, double quoted column names containing spaces/hyphens Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Knut P. Lehre @ 2003-06-20 20:24 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: plpgsql, double quoted column names containing spaces/hyphens
  2003-06-20 20:24 plpgsql, double quoted column names containing spaces/hyphens Knut P. Lehre <k.p.lehre@tiscali.no>
@ 2003-06-21 22:18 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2003-06-21 22:18 UTC (permalink / raw)
  To: Knut P. Lehre <k.p.lehre@tiscali.no>; +Cc: pgsql-sql

"Knut P. Lehre" <k.p.lehre@tiscali.no> writes:
> Using pgsql 7.2.1 (PeerDirect beta4) on WinXP Prof SP1. Client: MS 

> 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.

This is fixed in 7.3.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-06-21 22:18 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-20 20:24 plpgsql, double quoted column names containing spaces/hyphens Knut P. Lehre <k.p.lehre@tiscali.no>
2003-06-21 22:18 ` Tom Lane <tgl@sss.pgh.pa.us>

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