On Tue, Aug 11, 2009 at 6:35 AM, Sam Mason<s...@samason.me.uk> wrote:
> On Mon, Aug 10, 2009 at 03:43:45PM -0400, Tom Lane wrote:
>> "Kevin Grittner" <kevin.gritt...@wicourts.gov> writes:
>> > Tom Lane <t...@sss.pgh.pa.us> wrote:
>> >> "Kevin Grittner" <kevin.gritt...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
