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, Pennsylvania 19026