Add support for \clip command

[psacawa]

Description

When profiling Postgres queries with EXPLAIN ANALYZE, it is often necessary to copy the query to the system clipboard in order to feed the output to a visualisation tool like (depesz)[explain.depesz.com]. psql has no facilities for this, so I usually need to do something like

psql -qAt --no-psqlrc -f cmd.sql | xsel -ib

This is annoying, so I see the advantage of a \clip command that copies the DB output to the clipboard, which lets you do

\clip explain analyze select * from tab;

It is different from the native DB commands, so I don't see how to implement it cleanly. It doesn't belong in pgspecial. The present implementation is a bit ad hoc. It uses the package pyperclip, which implements a cross-platform clipboard client.

I am submitting this PR to solicit comments, with the hope to work toward a good solution. Please allow me to run workflows

Checklist

• [x] I've added this contribution to the changelog.rst.
• [x] I've added my name to the AUTHORS file (or it's already there).

• [x] I installed pre-commit hooks (pip install pre-commit && pre-commit install), and ran black on my code.
• [x] Please squash merge this pull request (uncheck if you'd like us to merge as multiple commits)

[codecov-commenter]

Codecov Report

Merging #1264 (9ad3233) into master (baea769) will decrease coverage by 0.33%. The diff coverage is 78.12%.

@@            Coverage Diff             @@
##           master    #1264      +/-   ##
==========================================
- Coverage   83.82%   83.48%   -0.34%     
==========================================
  Files          21       21              
  Lines        2559     2604      +45     
==========================================
+ Hits         2145     2174      +29     
- Misses        414      430      +16     

Impacted Files	Coverage Δ
pgcli/magic.py	0.00% <0.00%> (ø)
pgcli/pgexecute.py	79.40% <47.61%> (-2.53%)	:arrow_down:
pgcli/completion_refresher.py	91.66% <50.00%> (ø)
pgcli/main.py	75.79% <70.96%> (+0.02%)	:arrow_up:
pgcli/pgtoolbar.py	31.42% <71.42%> (+10.73%)	:arrow_up:
pgcli/config.py	91.93% <100.00%> (-0.93%)	:arrow_down:
pgcli/packages/parseutils/__init__.py	100.00% <100.00%> (ø)
pgcli/packages/parseutils/meta.py	93.33% <100.00%> (ø)
pgcli/packages/parseutils/tables.py	97.67% <100.00%> (-0.03%)	:arrow_down:
pgcli/packages/prioritization.py	100.00% <100.00%> (ø)
... and 6 more

---

Continue to review full report at Codecov.

Legend - Click here to learn more Δ = absolute <relative> (impact), ø = not affected, ? = missing data Powered by Codecov. Last update 803a9d3...9ad3233. Read the comment docs.

[amjith]

This does belong in pgspecial package. We do have custom commands that are not DB commands in pgspecial.

Take a look at the iocommands file in pgspecial.

Also have a look at the develop.rst file, it talks a bit about how to add special commands.

[amjith]

Here's a reference implementation from mycli: https://github.com/dbcli/mycli/blob/master/mycli/packages/special/iocommands.py#L165

[okbob]

psql has command \g |

postgres=# explain select * from o
obce         obce_id_seq  okresy       
postgres=# explain select * from obce limit 10 \g | wl-copy
postgres=# 

There are lot of use cases - copy to clipboard is just one.

[psacawa]

@okbob, thanks for informing me about this. It is definitely very useful. But for my case it isn't enough. This is because I typically need the raw JSON format for pev2, and the output of EXPLAIN (ANALYZE, format json) SELECT * from mytable \g | xsel -ib gives me:

QUERY PLAN              
-------------------------------------
 [                                  +
   {                                +
     "Plan": {                      +
       "Node Type": "Seq Scan",     +
       "Parallel Aware": false,     +
       "Relation Name": "mytable",  +
       "Alias": "mytable",          +
       "Startup Cost": 0.00,        +
       "Total Cost": 35.50,         +
       "Plan Rows": 2550,           +
       "Plan Width": 4,             +
       "Actual Startup Time": 0.005,+
       "Actual Total Time": 0.005,  +
       "Actual Rows": 0,            +
       "Actual Loops": 1            +
     },                             +
     "Planning Time": 0.094,        +
     "Triggers": [                  +
     ],                             +
     "Execution Time": 0.029        +
   }                                +
 ]
(1 row)

to the clipboard. So unless there is some some \pset option I don't know, then a more fine-grained command is required.

[okbob]

út 18. 5. 2021 v 10:07 odesílatel Paweł Sacawa @.***> napsal:

@okbob https://github.com/okbob, thanks for informing me about this. It is definitely very useful. But for my case it isn't enough. This is because I typically need the JSON format for pev2 https://explain.dalibo.com/, and the output of EXPLAIN (ANALYZE, format json) SELECT * from mytable \g | xsel -ib gives me:

QUERY PLAN

[ + { + "Plan": { + "Node Type": "Seq Scan", + "Parallel Aware": false, + "Relation Name": "mytable", + "Alias": "mytable", + "Startup Cost": 0.00, + "Total Cost": 35.50, + "Plan Rows": 2550, + "Plan Width": 4, + "Actual Startup Time": 0.005,+ "Actual Total Time": 0.005, + "Actual Rows": 0, + "Actual Loops": 1 + }, + "Planning Time": 0.094, + "Triggers": [ + ], + "Execution Time": 0.029 + } + ] (1 row)

So unless there is some some \pset option I don't know, then a more fine-grained is required.

It is solved in psql 13, where you can use one shot parameters for \g

explain (format json) select * from pg_class limit 10 \g (format=unaligned tuples_only) | wl-copy

[ { "Plan": { "Node Type": "Limit", "Parallel Aware": false, "Async Capable": false, "Startup Cost": 0.00, "Total Cost": 0.43, "Plan Rows": 10, "Plan Width": 265, "Plans": [ { "Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Relation Name": "pg_class", "Alias": "pg_class", "Startup Cost": 0.00, "Total Cost": 16.96, "Plan Rows": 396, "Plan Width": 265 } ] } } ]

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/dbcli/pgcli/pull/1264#issuecomment-842953319, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO47KNXDKNLTRRCNK2DDTOIN25ANCNFSM44D4RQKQ .