I had a pleasant surprise today when demonstrating a previous misfeature in PostgreSQL behaved unexpectedly. In further investigation, there is a really interesting syntax which is very helpful for some things I had not known about.
INSERT INTO keyvaltest VALUES ('foo', 'bar'), ('fooprime', 'barprime');
SELECT value(k) from keyvaltest k;
The latter performs exactly like
SELECT k.value from keyvaltest k;
So the column/function equivalent is there. This is probably not the best for production SQL code just because it is non-standard, but it is great for theoretical examples because it shows the functional dependency between tuple and tuple member.
It gets better:
CREATE OR REPLACE FUNCTION value(test) returns int language sql as $$
select 3; $$;
ERROR: "value" is already an attribute of type test
So this further suggests that value(test) is effectively an implicit function of test (because it is a trivial functional dependency).
So with all this in mind, is there any reason why we can't or shouldn't allow:
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?
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
