Re: Implicit coercions, choosing types for constants, etc

On Sun, 10 Nov 2002, Tom Lane wrote:

> In the last episode of this long-running issue, we decided that life
> would be better if we make small-enough integer constants be initially
> typed as int2 rather than int4, and then auto-promote them up to int4
> when necessary.

What kind of type system do postgresql (or SQL in general) use? I don't
know much about the postgresql codebase but I do know something about type
inference of functional languages.

It seems to me that one would like to have a polymorphic typesystem with
constraints to handle overloading, subtyping or whatever is needed in
SQL.

> This would solve problems with, for example,
> SELECT ... WHERE smallintcol = 42
> not using an index.

Using a suitable typesystem 42 could be said to have a type like

isInt z => z

which should be read that z is the type and isInt z is a constraint on z
saying that z is an integer type (that means for example that z can never
be instantiated to Bool). Then the use of smallintcol = 42 where
smallintcol has type int2 and where equality = is overloaded for types
Int2 -> Int2 -> Bool, Int4 -> Int4 -> Bool (and so on) would force 42 to
be the type Int2 in this case, since the first argument of = had type
Int2.

Is there anywhere I can read about the typesystem in SQL in general and
postgresql in particular?

There are a number of type systems in the functional world with properties
like this. Some very advanced and some simpler. I have a feeling from
reading this list that the type inference in postgresql as a bit ad hook.
But i've not read the source at all so it might be unfair to say such a
thing.

In the functional language Haskell there is also a defaulting rule that is
used if you end up with constants like 42 still with type isInt z => z. If
the type was left like this it just imply that any numeric type for 42
would do. In this case maybe z is defaulted to Int4. In most cases they
way you use the constant would force it to be a particular type.

Of the examples you gave in the letter I don't see anything that shouldn't
work with a more advanced typesystem like this. But I'm sure there are
other strange constructs in postgresql that I don't know about.

I would love to make some small toy implementation to try out things but
in the nearest future I don't have time for that. But this interests me so
maybe I'll give it a try some day (like next summer :-). At least I would
like to know more about how it works in postgresql today. It's possible
that what I talk about do not apply to SQL or that postgresql already
implements similar things.

--
/Dennis