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

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

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

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

> 5/ serial data type
> o Serial type must return inserted key value

How does Informix return the value?

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