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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026