ne 13. 2. 2022 v 9:29 odesílatel Peter J. Holzer <hjp-pg...@hjp.at> napsal:
> On 2022-02-13 01:11:16 +0100, Andreas 'ads' Scherbaum wrote: > > On 12/02/2022 22:34, Peter J. Holzer wrote: > > > On 2022-02-12 22:09:25 +0100, Andreas 'ads' Scherbaum wrote: > > > > On 12/02/2022 20:50, Peter J. Holzer wrote: > > > > > On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote: > > > > > > On 10/02/2022 18:22, Peter J. Holzer wrote: > > > > > > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote: > > > > > > > > Examples of small things Postgres could have: > > > > > > > > > > > > > > > > • SELECT * - b.a_id from a natural join b > > > [...] > > > > > > Maybe for this specific use case it's easier to teach psql how > to do that, > > > [...] > > > > > I think the easiest way to get the columns would be to > EXPLAIN(verbose) > > > > > the query. Otherwise psql (or whatever your shell is) would have to > > > > > completely parse the SQL statement to find the columns. > [...] > > > > I was thinking about this problem for a while, and it's not easy to > solve. > > > > Hence I came up with the idea that psql could - once the table is > known > > > > and very specific psql syntax is there (\- as example) replace the * > with > > > > the actual columns. All of this before the query is run, and as a > user you > > > > can edit the column list further. > > > Yeah, but the problem is that it isn't that easy for psql to figure out > > > which table is involved. > > > > The complaint is not about complex queries, or CTEs, or Joins. This is > > about simple queries where a user wants to discover - surf - the database > > and look into specific tables, but exclude certain columns. > > If you look back through this thread you will notice that it was me who > brought up that specific scenario. You might trust me to know what I was > thinking about when I wrote it ;-). > > > More specifically, this is when the user types in interactive queries. > > I do write quite complex queries interactively. Even when "surfing", > joins are common because in a normalized schema many columns just > contain meaningless foreign keys. But I also do some data analysis > interactively (which is where CTEs usually come in) and I like to > develop queries interactively before putting them into (Python) programs. > > But even if I didn't do that - if you add a feature like that to psql, > it should work for all queries, not just a tiny subset. > > > Today psql tries to do autocomplete for certain scenarios, this too does > > not work in complex queries, but nevertheless is a useful help if someone > > tries to run simple, interactive queries. > > Autocomplete doesn't even work well for simple queries. It's better than > nothing, but I don't think that it should serve as a model for new > features. > The MySQL autocomplete is designed without context filtering. Maybe we can have this implementation too (as alternative) so using all column names + all table names + aliases.column names (when we know defined alias) Another idea about column excluding. Any implementation on the client side is very complex, because you need to parse sql. But maybe we can enhance SQL with some syntax. SELECT ... and LAST optional clause can be HIDE, OR EXCLUDE NAME or EXCLUDE TYPE SELECT * FROM postgistab HIDE TYPE LIKE 'wkb%' SELECT * FROM postgistab ORDER BY ... EXCLUDE COLUMN LIKE 'shape%' WITH x AS (SELECT * FROM xx) SELECT * FROM x EXCLUDE COLUMN x1,x2 The column excluding should be separate *last* clase. More with this syntax is less possibility of collision with ANSI SQL What do you think about it? Regards Pavel Implementation on the server side can be pretty easy then. > > hp > > -- > _ | Peter J. Holzer | Story must make more sense than reality. > |_|_) | | > | | | h...@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
