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

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.

--  Sam  http://samason.me.uk/