
	Dear Sirs!:)I encounted one small problem,working with 
PostgreSQL 7.3devel.It can look a
bit strange,but i have to use whitespaces in names of databases,tables,fields
and so on(like "roomno jk").It's possible to create them all and work with them
(INSERT,DELETE,UPDATE),but PL/pgSQL parser(compiler ?) can't execute such 
statements.To explain the problem, I took and changed next examples from 
pgsql/src/pl/plpgsql/test:

-- ************************************************************ 
-- * Tables for the patchfield test of PL/pgSQL 
-- * $Header: /projects/cvsroot/pgsql/src/pl/plpgsql/test/tables.sql,v 1.1 1998/08/24 19:16:27 momjian Exp $
-- ************************************************************

create table Room (
    "roomno jk"	char(8), --- common SQL parser eats it
    comment	text
);
create unique index Room_rno on Room using btree ("roomno jk"  bpchar_ops);

create table WSlot (
    slotname	char(20),
    "roomno jk"	char(8),  --- common SQL parser eats it
    slotlink	char(20),
    backlink	char(20)
);
create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
 
You also can use such "roomno jk" in DECLARATION of PL/pgSQL procedures and functions :
 
-- ************************************************************
-- * Trigger procedures and functions for the patchfield
-- * test of PL/pgSQL
-- * $Header: /projects/cvsroot/pgsql/src/pl/plpgsql/test/triggers.sql,v 1.2 2000/10/22 23:25:11 tgl Exp $
-- ************************************************************
-- * AFTER UPDATE on Room
-- *	- If room no changes let wall slots follow
-- ************************************************************

PL/pgSQL eats it,he will cry during execution.

create function tg_room_au() returns opaque as '
begin
    if new."roomno jk"  != old."roomno jk" then
        update WSlot set "roomno jk" = new."roomno jk" where "roomno jk" = old."roomno jk";
    end if;
    return new;
end;
' language 'plpgsql';

create trigger tg_room_au after update
    on Room for each row execute procedure tg_room_au();

-- ************************************************************
-- * BEFORE INSERT or UPDATE on WSlot
-- *	- Check that room exists
-- ************************************************************

PL/pgSQL also eats it,he will cry during execution.

create function tg_wslot_biu() returns opaque as '
begin
	if count(*) = 0 from Room where "roomno jk" = new."roomno jk" then
        raise exception ''Room % does not exist'', new."roomno jk";
    end if;
    return new;
end;
' language 'plpgsql';


create trigger tg_wslot_biu before insert or update
    on WSlot for each row execute procedure tg_wslot_biu();

Then do next:
insert into Room values ('001', 'Entrance'); --Everything is ok

Then do it and catch failure:
insert into WSlot values ('WS.001.1a', '001', '', '');

PostgreSQL returns :

psql:/home/eu/SQL/plt/p_test.sql:19: ERROR:  parse error at or near "new"
psql:/home/eu/SQL/plt/p_test.sql:20: WARNING:  plpgsql: ERROR during compile of tg_wslot_biu near line 3

As you see there's no support for "roomno jk" in PL/pgSQL parser.
To this moment i know nothing serious about flex,lex and yacc,but
a simple look at PL/pgSQL parser shows,that situations of
"roomno jk" are just undefined there.
					regards,Eugene
P.S.In case you make patch,please,send me a copy. 












Fri, 12 Jul 2002 14:31:18 +0400 (MSD)
"eutm" <eutm@yandex.ru>
Bug of PL/pgSQL parser
I see this on the TODO list:

# Fix PL/PgSQL to handle quoted mixed-case identifiers


Perhaps you could make a view (alias the names with spaces) to work on?


On Fri, 2002-07-12 at 06:31, eutm wrote:
> 
> 	Dear Sirs!:)I encounted one small problem,working with 
> PostgreSQL 7.3devel.It can look a
> bit strange,but i have to use whitespaces in names of databases,tables,fields
> and so on(like "roomno jk").It's possible to create them all and work with them
> (INSERT,DELETE,UPDATE),but PL/pgSQL parser(compiler ?) can't execute such 
> statements.To explain the problem, I took and changed next examples from 
> pgsql/src/pl/plpgsql/test:
> 
> -- ************************************************************ 
> -- * Tables for the patchfield test of PL/pgSQL 
> -- * $Header: /projects/cvsroot/pgsql/src/pl/plpgsql/test/tables.sql,v 1.1 1998/08/24 19:16:27 momjian Exp $
> -- ************************************************************
> 
> create table Room (
>     "roomno jk"	char(8), --- common SQL parser eats it
>     comment	text
> );
> create unique index Room_rno on Room using btree ("roomno jk"  bpchar_ops);
> 
> create table WSlot (
>     slotname	char(20),
>     "roomno jk"	char(8),  --- common SQL parser eats it
>     slotlink	char(20),
>     backlink	char(20)
> );
> create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
>  
> You also can use such "roomno jk" in DECLARATION of PL/pgSQL procedures and functions :
>  
> -- ************************************************************
> -- * Trigger procedures and functions for the patchfield
> -- * test of PL/pgSQL
> -- * $Header: /projects/cvsroot/pgsql/src/pl/plpgsql/test/triggers.sql,v 1.2 2000/10/22 23:25:11 tgl Exp $
> -- ************************************************************
> -- * AFTER UPDATE on Room
> -- *	- If room no changes let wall slots follow
> -- ************************************************************
> 
> PL/pgSQL eats it,he will cry during execution.
> 
> create function tg_room_au() returns opaque as '
> begin
>     if new."roomno jk"  != old."roomno jk" then
>         update WSlot set "roomno jk" = new."roomno jk" where "roomno jk" = old."roomno jk";
>     end if;
>     return new;
> end;
> ' language 'plpgsql';
> 
> create trigger tg_room_au after update
>     on Room for each row execute procedure tg_room_au();
> 
> -- ************************************************************
> -- * BEFORE INSERT or UPDATE on WSlot
> -- *	- Check that room exists
> -- ************************************************************
> 
> PL/pgSQL also eats it,he will cry during execution.
> 
> create function tg_wslot_biu() returns opaque as '
> begin
> 	if count(*) = 0 from Room where "roomno jk" = new."roomno jk" then
>         raise exception ''Room % does not exist'', new."roomno jk";
>     end if;
>     return new;
> end;
> ' language 'plpgsql';
> 
> 
> create trigger tg_wslot_biu before insert or update
>     on WSlot for each row execute procedure tg_wslot_biu();
> 
> Then do next:
> insert into Room values ('001', 'Entrance'); --Everything is ok
> 
> Then do it and catch failure:
> insert into WSlot values ('WS.001.1a', '001', '', '');
> 
> PostgreSQL returns :
> 
> psql:/home/eu/SQL/plt/p_test.sql:19: ERROR:  parse error at or near "new"
> psql:/home/eu/SQL/plt/p_test.sql:20: WARNING:  plpgsql: ERROR during compile of tg_wslot_biu near line 3
> 
> As you see there's no support for "roomno jk" in PL/pgSQL parser.
> To this moment i know nothing serious about flex,lex and yacc,but
> a simple look at PL/pgSQL parser shows,that situations of
> "roomno jk" are just undefined there.
> 					regards,Eugene
> P.S.In case you make patch,please,send me a copy. 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 



12 Jul 2002 08:51:50 -0400
Rod Taylor <rbt@zort.ca>
Re: Bug of PL/pgSQL parser
"eutm" <eutm@yandex.ru> writes:
> 	Dear Sirs!:)I encounted one small problem,working with 
> PostgreSQL 7.3devel.It can look a
> bit strange,but i have to use whitespaces in names of databases,tables,fields
> and so on(like "roomno jk").It's possible to create them all and work with them
> (INSERT,DELETE,UPDATE),but PL/pgSQL parser(compiler ?) can't execute such 
> statements.

Yeah, this is a known bug: the plpgsql lexer doesn't really handle
quoted identifiers correctly.  (It effectively acts like double-quote
is just another letter, which of course falls down on cases like
embedded whitespace.)  If you have any experience with writing flex
rules, perhaps you'd care to submit a patch.

			regards, tom lane

Fri, 12 Jul 2002 11:14:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug of PL/pgSQL parser 