W dniu 24.10.2015 o 23:25, Dane Foster pisze: > > On Sat, Oct 24, 2015 at 5:23 PM, Dane Foster <studdugie@xxxxxxxxx > <mailto:studdugie@xxxxxxxxx>> wrote: > [--------------------] > -- > Sent via pgsql-general mailing list > (pgsql-general@xxxxxxxxxxxxxx <mailto:pgsql-general@xxxxxxxxxxxxxx>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > You may be able to accomplish that using aliased sub-selects as > in-line views. The purpose of the sub-selects in this use-case is > simply to cherry pick the columns you want. > SELECT * > FROM > (SELECT col1, col2, col4 FROM tablea) AS iv > JOIN (SELECT co1, col3, col5 FROM tableb) AS tb1 USING (col1)) > JOIN (SELECT col1, col6, col7 FROM tableb) AS tb22 USING (col1) > > Please note, this may be a performance nightmare for large tables > because w/o a WHERE clause that can be pushed down to the > sub-selects each sub-select will do a full table scan. Yes. And that's why this is not truely an option. I'd rather give all coluns aliases (when coding), then opt for subquery on every execute. > > Please note that the 3rd JOIN clause is nutty (I translated it from > your original) because why would you join a table to itself just to > select a different set of columns? One example (a bit artificial, I know) might be the address data, for waybill: create table purchases( basket int, customer int, delivery int, ...); select * from purchases p join buyers b(customer, city, address) using (customer) join buyers d (delivery, to_city, to_address, to_zip) using (delivery); ... or something like that. ZIP code is not actually needed to indicate customer (SSN might be instead). But I wouldn't agrue if real life programming actually needs that. I've just wanted to have the most generic example I've imagined. > > Good luck, > > Dane > > > For the record SELECT * in my example is absolutely the wrong thing to > do but your original didn't leave me w/ any other option. > Hmmm. I've seen people say that. I do keep that in mind, but frankly I actually never had to avoid that to get my code working (and maintained). I do that sometimes to limit the bandwidth necesery to deliver the results, but not so often. But I'd say, that "the standard" sort of does that (i.e. the star) notoriusly: 1. with table aliasing (the case we are discussing now), standard expects us to give column aliases *in order* they are defined within the aliased table - that's nothing else but a "hidden star" somwhere there. And I really wish they did it without that. 2. see the systax of INSERT: a list of column names to be prowided with values is optional, and when you don't give it, it's like you've written "a star" in its place. This I find *very bad* and never use it myself. But standard people thought otherwise. So personally, I don't see a star in a select list so harmfull, quite the oposit. -R -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
