Re: [GENERAL] Missing SQL Syntax & Problem with Create Table

On Mon, 8 Jun 1998, Fredrick Meunier wrote:

> Hi,
>
> Jose' Soares Da Silva wrote:
> > On Mon, 8 Jun 1998, Fredrick Meunier wrote:
> > > CREATE VIEW ALL_TEXT (TEXTIDTF,TEXTNSEG)
> > > AS SELECT B.BLBJ,B.NSEG FROM BLBJ B WHERE B.BTYP = 1
> > > ERROR from backend during send_query: 'ERROR: parser: parse error
> > > at or near "("'
> > PostgreSQL doesn't support the above syntax. Try this instead:
> >
> > CREATE VIEW ALL_TEXT
> > AS SELECT B.BLBJ AS TEXTIDTF, B.NSEG AS TEXTNSEG
> > FROM BLBJ B WHERE B.BTYP = 1
> Sorry if I was not clear, but the tool I am using (Powersoft's Power
> Designer) does not let me change the queries it makes for maintaining
> it's Metadata repository, I was asking when (if?) there may be support
> for this syntax in PostgreSQL.
Well, this syntax is SQL92 but PostgreSQL doesn't support it yet,
I'm afraid there's not a work around to solve this problem.
>
> > > What are the chances of getting view creation syntax like the above
> > > accepted?
> > >
> > > The other problem is:
> > > CREATE TABLE MPDREFR
> > > ( REFR int4 NOT NULL,
> > > SRCE int4 NOT NULL,
> > > TRGT int4 NOT NULL,
> > > LABL varchar(254) ,
> > > URUL int2 ,
> > > DRUL int2 ,
> > > MAND int2 ,
> > > CPRT int2 ,
> > > TOBJ int2 ,
> > > COBJ varchar(80) ,
> > > SOID int4 ,
> > > FKCN varchar(64) ,
> > > CMIN varchar(10) ,
> > > CMAX varchar(10) ,
> > > NGEN int2 )'
> > > ERROR from backend during send_query: 'ERROR: create: system
> > > attribute named "cmin"'
> >
> > cmin and cmax are reserved words, try to rename to C_MIN C_MAX for
> > example.
> Again, I can't change the tool's schema definition, but since cmin and
> cmax are legal SQL92 column names I was wondering if there were any
> enhancements than could be made to PostgreSQL's system attributes to
> prevent clashes with SQL92-legal names.
>
> > > Can the system attribute limitation be removed, or can the system
> > > attributes be renamed to not conflict with legal SQL92 column names?

PostgreSQL has five internal column names that you can't use
take a look:

create table niente (avoid int);
insert into niente values (1);
select oid,cmin,cmax,xmin,xmax from niente;

oid|cmin|cmax| xmin|xmax
------+----+----+-----+----
199369| 0| 0|45781| 0
(1 row)

from man sql...

FIELDS AND COLUMNS
Fields
A field is either an attribute of a given class or one of
the following:
oid, xmin, xmax, cmin, cmax.

Oid stands for the unique identifier of an instance which
is added by Postgres to all instances automatically. Oids
are not reused and are 32 bit quantities.

Xmin, cmin, xmax and cmax stand respectively for the iden
tity of the inserting transaction, the command identifier
within the transaction, the identity of the deleting
transaction and its associated deleting command. For fur
ther information on these fields consult [STON87].

I think that it's impossible to change this names.
But you can send this question to the psql-hackers list.
Ciao, Jose'