Re: Re: Feature proposal and discussion: full-fledged column/function equivalence

On Fri, Aug 1, 2014 at 6:22 PM, Chris Travers <chris(dot)travers(at)gmail(dot)com>
wrote:

> On Fri, Aug 1, 2014 at 12:19 PM, David G Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> Vik Fearing wrote
>> >> CREATE testfunction(test) returns int language sql as $$ select 1; $$;
>> >> SELECT testfunction FROM test;
>> >>
>> >> That would allow first-class calculated columns.
>> >>
>> >> I assume the work is mostly at the parser/grammatical level. Is there
>> >> any reason why supporting that would be a bad idea?
>> >
>> > This is already supported since forever.
>> >
>> > SELECT test.testfunction FROM test;
>>
>> More to the point: if you are writing a multiple-relation query and have
>> "testfunction" functions defined for at least two of the relations used in
>> the query how would the system decide which one to use?
>>
>
> Same way you do it for columns. Throw an error that it is ambiguous.
>
>

​I'd rather approach the first-class issue by being able to say: ALTER
TABLE test ADD COLUMN ​testfunction(test) -- maybe with an "AS col_alias"...

I do not have anything particularly against your proposal but neither do I
find it an overwhelming improvement over "testfunction(test) and
test.testfunction" - especially when I can encapsulate them behind a VIEW.

>
>> SELECT testfunction FROM test JOIN test_extended USING (test_id)
>>
>> I guess you could allow for the non-ambiguous cases and error out
>> otherwise
>> but that seems to be adding quite a bit of complexity for little gain.
>>
>
> Hmm. As I see it, there is one possible backwards compatibility issue but
> it is almost certainly extraordinarily rare.
>
> Suppose in your above example, test_extended has a testfunction attribute
> but test has a testfunction function. In the current codebase, there is no
> parsing ambiguity (the attribute wins because the function is ignored), but
> we'd have to throw the same error as if the function were an attribute if
> we did this.
>
> It doesn't seem terribly logically complicated to do this (since it is a
> slight extension to the lookup in the system catalogs), and I am having
> trouble imagining that there are many cases where these sorts of functions
> are added.
>
> The larger question becomes:
>
> Would it be more useful to have such functions in the select * result, or
> to treat them as hidden columns from that? (I am thinking that can be
> decided down the road though if I go through and take this up on -hackers).
>
>>
>>
>>
If they truly are "first class" members of the table they should probably
appear with " SELECT * "; otherwise, and this is simply semantics, you are
simply adding yet another syntax to remember to invoke a function since the
user will still have to know said function exists. I read "first class" to
mean that the fact the value is being derived from a function call is
invisible to the user. And this then points leads back to the idea of
defining a generated column on the actual table or, in absence of that
capability - live with the fact the updateable can accomplish many, if not
all, of the same goals today.

David J.