Re: Patch for 8.5, transformationHook - Mailing list pgsql-hackers

On Tue, Aug 11, 2009 at 6:35 AM, Sam Mason<sam@samason.me.uk> wrote:
> On Mon, Aug 10, 2009 at 03:43:45PM -0400, Tom Lane wrote:
>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> >>> Still, it rates pretty high on my astonishment scale that a
>> >>> COALESCE of two untyped NULLs (or for that matter, any two values
>> >>> of unknown type) returns a text value.
>> >>
>> >> What would you have it do instead, throw an error?
>>
>> > Return a value of unknown type.
>>
>> That would require doing actual computation on values of unknown type.
>
> A better way would be to say it's of polymorphic type.  PG's support of
> polymorphism is currently a bit ad-hoc, but this would be something I'd
> love to change.  It would be quite a big change and I've not thought
> through all the details yet.
>
>> In the specific case of COALESCE, we could theoretically do that,
>> since the only computation it needs is "IS NULL" which is
>> datatype-independent.
>
> Yes, this would be the only valid operator I can see working.  COUNT
> would work as an aggregate.
>
>> In most situations, however, you can't evaluate
>> the function without knowledge of the datatype semantics.  As an
>> example, consider NULLIF('0', '00').  This gives different answers if
>> you suppose the literals are text than if you suppose they are integers.
>
> Yup, which is when it gets fun and I think would mean we'd end up
> throwing out a few more queries as ambiguous if I had my way!
>
> As long as there was *one* type in the above expression then it would
> be OK, for example it would be unambiguous in either of the following
> cases:
>
>  SELECT NULLIF(INT '0', '00');
>  SELECT NULLIF('0', INT '00');
>
> and I'd also like the following to be OK:
>
>  SELECT NULLIF('0', '00') + 5;
>  SELECT n+5 FROM (SELECT NULLIF('0', '00')) x(n);
>
> But PG currently throws these out as it's type resolution (also known
> as type unification) is too eager.  The same arguments would obviously
> apply to any polymorphic function.  For example, I'd expect to be able
> to do:
>
>  SELECT ('{1,2}')[1] + 5;
>
> and have PG figure out that the literal is of type INT[].  Not sure what
> ambiguity is being prevented that causes PG to need the brackets, but
> that's a side issue.
>
> It also raises the issue of the fact that there's no general way
> to ascribe types in PG.  You can cast (using a couple of different
> syntaxes) but this isn't the same as type ascription.  For example, I'd
> like to be able to do things like:
>
>  SELECT NULLIF('0', '00')::INT + 5;
>
> But I'm doing a cast here, I'm not saying that the NULLIF function
> evaluates to a value of type INT which is what I want to be doing.  So
> this currently results in 5 being returned and not NULL as I really
> want.  The above obviously isn't the syntax to use as it would break
> code, but the functionality would be useful.

What you're talking about here is called "type inference".

http://en.wikipedia.org/wiki/Type_inference

...Robert