My point was (and still is) that standard SQL is horrible, and there are way better solutions.
Real world usage makes SQL vendors extend SQL to make it less sucky; some of these extensions were later encoded into standard, and some are still proprietary.
Windowing functions are nice and all, but are a complex solution to a problem that would hardly exist if you actually embraced order as fundamental.
Ok, how about the most useful kdb+ extension (which I forgot about earlier): foreign key chasing: if table t has field a which has a foreign key reference to table s (which has field b which has a foreign key reference to table r (which has field c which has a foreign key ...)
in kdb+, you do:
select a.b.c from t
Does pgsql have something similar? Or do you have to spell out all the joins?
can foreign key chasing handle composite primary/foreign key joins?
You can build something to do this in PostgreSQL using stored procedures and a (a.b).c syntax but that's kind of advanced stuff. To do this you have to create a b function such that b(a) returns tuple of type b which has column or function c.
Example:
create table address (...)
create table employee (...., address_id);
create function address(employee) returns address as $$...$$;
select (employee.address).country from employee; will then return the country field from the address returned by address(employee).
So yeah, kinda, if you build your own.
edit: I would be willing to bet you could make an implicit join operator of this sort also but I haven't done so. I don't know what the performance ramifications would be of throwing this into the column list.
> can foreign key chasing handle composite primary/foreign key joins?
Yes. The only requirement for foreign key chasing to work is that it uniquely identifies one record in the foreign table. Whether that key is atomic or composite is of no consequence.
(internally kdb+ stores a pointer to the foreign record when it verifies the existence of said record on insert, so it doesn't have to do a join query - it always knows exactly which record to bring in. So in practice, it is very efficient regardless of what kind of indexes you might have in place, the size or the composition of the foreign key field)
> So yeah, kinda, if you build your own. I would be willing to bet you could make an implicit join operator of this sort also but I haven't done so.
pgsql is a wonderful beast. I really like it. And I would be even happier if they adopted some kdb+/q syntax and semantics, though I don't think that's likely to happen.
Real world usage makes SQL vendors extend SQL to make it less sucky; some of these extensions were later encoded into standard, and some are still proprietary.
Windowing functions are nice and all, but are a complex solution to a problem that would hardly exist if you actually embraced order as fundamental.
Ok, how about the most useful kdb+ extension (which I forgot about earlier): foreign key chasing: if table t has field a which has a foreign key reference to table s (which has field b which has a foreign key reference to table r (which has field c which has a foreign key ...)
in kdb+, you do:
Does pgsql have something similar? Or do you have to spell out all the joins?