On Wed, 24 Apr 2024 at 14:43, Thom Brown <t...@linux.com> wrote: > On Tue, 23 Apr 2024 at 13:50, Dave Page <dp...@pgadmin.org> wrote: > >> >> >> On Tue, 23 Apr 2024 at 12:03, Thom Brown <t...@linux.com> wrote: >> >>> >>>> You've been able to do the "Select and run" thing for years. If you >>>> select text in the editor and hit the execute button, only the selected >>>> text is sent to the server. If nothing is selected, the entire string is >>>> sent. This feature will complement that for convenience, but for safety >>>> will have a separate button/shortcut. >>>> >>> >>> Oh, I clearly don't use PgAdmin enough to know this already. >>> >> >> Boo! >> >> >>> >>> I still find the proposal somewhat unintuitive, but the foot-gun >>> safeguards that have been suggested sound like any pedal injuries will >>> solely be the fault of the user. >>> >>> I would want to see it tested in a diverse range of scenarios though, >>> which will require some imagination given what users will no doubt try to >>> use it on. >>> >> >> Yes, I have made that very clear to the team. Suggestions for test >> scenarios are welcome of course - a good way to experiment might be to see >> how the current version of pgAdmin (which uses the new CodeMirror code) >> manages to mess up syntax highlighting of anything weird. >> > > I guess here's a few to try out: >
Very helpful - thanks Thom! > > -- Put the cursor on every relation name, and every SELECT, DELETE and > INSERT > WITH deleted_rows AS ( > DELETE FROM mytable WHERE id IN ( > -- Does this run on its own? > SELECT id FROM mytable > ) > RETURNING id, content > ), > move_rows AS ( > INSERT INTO newtable > -- Does this SELECT run on its own, or does it backtrack to the INSERT? > SELECT id, content > FROM deleted_rows > ), > combined_result AS( > SELECT tableoid::regclass, id, content > FROM mytable > UNION ALL > -- Does this SELECT get run on its own? > SELECT tableoid::regclass, id, content > FROM newtable > ) > -- Does this SELECT get run on its own? > SELECT id, content > INTO backuptable > FROM combined_result; > > > SELECT id, content > FROM ( > /* > We are just performing: > SELECT id, content > FROM newtable; > ... at 2 levels > Does that commented query above highlight? > > Does each level of the query and nested queries run correctly? > */ > SELECT id, content, 'dummy1' > FROM ( > SELECT id, content, 'dummmy1', 'dummy2' > FROM newtable > ) > ); > > > DO LANGUAGE plpgsql $SELECT$ > DECLARE > myrec RECORD; > -- Does either SELECT in the cursor try to run when under PgAdmin's > cursor? > -- Is there any backtracking when selecting the 2nd one? > mycur CURSOR FOR SELECT 1 FROM (SELECT (VALUES (1))); > BEGIN > SELECT INTO STRICT myrec FROM ( > -- Does selecting the following SELECT correctly run without going > -- into the SELECT INTO? > SELECT > -- Can you run the query that appears in the value? > $$SELECT * FROM mytable$$ AS query, > > -- What happens when you select either of these SELECTs? > 'SELECT' AS "SELECT", > > -- And what happens on each one of these 4 DELETEs > $DELETE$DELETE$DELETE$ AS "DELETE" > ); > END > $SELECT$; > > None of this renders incorrectly in PgAdmin though. > > Thom > -- Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org EDB: https://www.enterprisedb.com
