Re: [HACKERS] Enhancing PGSQL to be compatible with Informix SQL

On Thu, 6 Jan 2000, Bruce Momjian wrote:

> > I am currently looking into the possibility of extending the current
> > postgres SQL implementation to be compatible with Informix's SQL
> > implementation.
> >
> > The changes required seem relatively straightforward, with one notable
> > exception.
>
> I am very familiar wit Informix.
>
> >
> > Requirements:
> > 1/ Datetime type specifiers (should have no impact)
> > o informix uses datetime specifiers of the form
> > DATETIME YEAR TO HOUR. (which is just the year,
> > month, day and hour portion of a datetime).
>
> I have to admit I usually find this very confusing with Informix.

I can't disagree. The way informix decided to do DATETIME stuff is
definately odd. That said, from a calcualtion standpoint you can pretty
much ignore the qualifier during calcualtions, its only really important
in the representation. (I'm actually making assumptions here, and it
produces considerable work at the representation stages, but that can
easily be accomodated).

>
> > 2/ Interval type specifiers (ditto)
> > o informix uses interval specifiers of the form
> > INTERVAL DAY TO HOUR. (which is just the
> > day and hour portion of an interval).
>
> This I can usually understand, though I think we can do this too clearer
> than Informix.
>
> > 3/ Money type specifiers
> > o informix has money type specifiers that are akin
> > to decimal speicifiers
>
> We have a MONEY type now, and are looking to invisibly use DECIMAL for
> this instead.
>

This would actually be sensible. My comment about money, is that the
existing type doesn't have a concept of precision; two decimal places of
money is somewhat meaningless where in the local currency it takes 1000
washers to buy a packet of crisps. The ability to set the precision of
the MONEY type is kinda important in this case.

> > 4/ Informix outer join syntax
> > o informix uses outer joins of the form
> > SELECT * FROM a, outer b where a.nr = b.nr
> > This will require some post-processing to determine
> > the actual join conditions.
>
> Believe it or not, I am hoping to get this into 7.0. The ANSI syntax
> requires a lot of optimizer changes, because it basically allows user
> specification of the join order. In talking to Thomas, we hoped to
> implement OUTER as a flag on the table that we could easily implement in
> 7.0. Let's see how it goes.
>

Sounds great! :)

> > 5/ serial data type
> > o Serial type must return inserted key value
>
> How does Informix return the value?
>

>From a user standpoint it mystically appears in sqlca just after the
insert statement is executed. Actually the informix engine recognises
it's just done a serial insert, and sends it back in addition to the
standard status packets.

> > o Unfortunately (and this is the big bad hit)
> > informix's serial datatype does serial number
> > generation on a zero inserted valued.
> > The modification required to do this may have
> > impact on existing programs.
>
> Yes, I have been thrown off by this. We don't allow a zero to
> auto-number. You have to use nextval('sequence_name') in the query to
> supply the sequence value, not a zero. I can see this as a pain, but
> the developers think the 0 replace with nextval() thing is strange and
> non-intuitive. The current behavior fits in the DEFAULT column
> activation in a logical way. I don't think I can get people to make
> this change. The 0 replacement is a behind the scenes thing, while
> DEFAULT and nextval() calls are logically consistent.
>

I can understand the situation here (one of the main reasons I raised the
thread in the first place). Above all else the difficulty I have with
serial at the moment is the impossibility of differentiating a serial with
an int4 after creation (after all the database treats them identically).
The catalog tables don't contain any information. The only way you can
work out you created a serial column is by looking for an appropriately
named sequence in the database on every int4 column that exists (or am I
wrong?). This is not exactly something that appeals to me

Also, in order to get correct returns from the serial column insert it
seems likely that the serial type would have to gain some kind of extra
special processing within the database above what it has already. In this
case all of the required behaviour could probably be implemented.

> > I'd be interested if anyone can see any conceptual difficulties i've
> > missed in these definitions, and welcome any concepts on the
> > implementation.
>
> I agree Informix compatibility is a good thing.
>
> --
> Bruce Momjian | http://www.op.net/~candle
> maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>

.............................Rod

+-----------------------------------------------------------------------------+
| Rod Chamberlin | rod(at)querix(dot)com Tel +44 1703 232345 |
| Software Engineer | Mob +44 7803 295406 |
| QueriX | Fax +44 1703 399685 |
+-----------------------------------------------------------------------------+
| The views expressed in this document do not necessarily represent those of |
| the management of QueriX (UK) Ltd. |
+-----------------------------------------------------------------------------+