Обсуждение: Discussion: psql \et -> edit the trigger function
We already have
\ef
\ev
The use case here is simply that it saves me from:
\d <table>
[scroll through all the fields]
[often scroll right]
select function name
\ef [paste function name]
and tab completion is much narrower
When doing conversions and reviews all of this stuff has to be reviewed.
Oftentimes, renamed, touched.
I am 100% willing to write the code, docs, etc. but would appreciate feedback.
Kirk...
Hi
st 10. 5. 2023 v 17:33 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:
We already have\ef\evThe use case here is simply that it saves me from:\d <table>[scroll through all the fields][often scroll right]select function name\ef [paste function name]and tab completion is much narrowerWhen doing conversions and reviews all of this stuff has to be reviewed.Oftentimes, renamed, touched.I am 100% willing to write the code, docs, etc. but would appreciate feedback.
\et can be little bit confusing, because looks like editing trigger, not trigger function
what \eft triggername
?
regards
Pavel
Kirk...
Kirk Wolak <wolakk@gmail.com> writes:
> We already have
> \ef
> \ev
>
> The use case here is simply that it saves me from:
> \d <table>
> [scroll through all the fields]
> [often scroll right]
> select function name
> \ef [paste function name]
>
> and tab completion is much narrower
I think it would make more sense to model it on the filtering letters
available for \df:
\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]
list [only agg/normal/procedure/trigger/window] functions
I just noticed that tab completion after e.g. \dft does not take the
function type restriction into account, so the solution for \ef<letters>
should be made to work for both. I wonder if it would even be possible
to share the tab completion filtering conditions with the actual
implementation of \df.
Also, I notice that \df only tab completes functions (i.e. not
procedures), although it actually returns all routines.
> When doing conversions and reviews all of this stuff has to be reviewed.
> Oftentimes, renamed, touched.
>
> I am 100% willing to write the code, docs, etc. but would appreciate
> feedback.
I'm happy to assist with and review at least the tab completion parts of
this effort.
> Kirk...
- ilmari
On Wed, May 10, 2023 at 12:20 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hist 10. 5. 2023 v 17:33 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:We already have\ef\evThe use case here is simply that it saves me from:\d <table>[scroll through all the fields][often scroll right]select function name\ef [paste function name]and tab completion is much narrowerWhen doing conversions and reviews all of this stuff has to be reviewed.Oftentimes, renamed, touched.I am 100% willing to write the code, docs, etc. but would appreciate feedback.\et can be little bit confusing, because looks like editing trigger, not trigger functionwhat \eft triggername?
Pavel, I am "torn" because of my OCD, I would expect
\eft <TAB>
to list functions that RETURN TRIGGER as opposed to the level of indirection I was aiming for.
where
\et <TAB>
Would specifically let me complete the Trigger_Name, but find the function
It makes me wonder, now if:
\etf
Is better for this (edit trigger function... given the trigger name).
And as another poster suggested. As we do the AUTOCOMPLETE for that, we could address it for:
\ef?
because:
\eft <TAB>
is valuable as well, and deserves to work just like all \ef? items
It seems like a logical way to break it down.
regardsPavelKirk...
Kirk Wolak <wolakk@gmail.com> writes:
> \et <TAB>
> Would specifically let me complete the Trigger_Name, but find the function
Hmm, I wonder how useful that's really going to be, considering
that trigger names aren't unique across tables. Wouldn't it
need to be more like "\et table-name trigger-name"?
Also, in a typical database I bet a large fraction of pg_trigger.tgname
entries are going to be "RI_ConstraintTrigger_something". Are we going
to suppress those?
regards, tom lane
I wrote:
> Hmm, I wonder how useful that's really going to be, considering
> that trigger names aren't unique across tables. Wouldn't it
> need to be more like "\et table-name trigger-name"?
Different line of thought: \et seems awfully single-purpose.
Perhaps we should think more of "\st table-name trigger-name"
(show trigger), which perhaps could print something along the
lines of
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt');
CREATE FUNCTION public.trigger_func()
RETURNS trigger
... the rest like \sf for the trigger function
If you indeed want to edit the function, it's a quick copy-and-paste
from here. But if you just want to see the trigger definition,
this is more wieldy than looking at the whole "\d table-name"
output. Also we have less of an overloading problem with the
command name.
regards, tom lane
st 10. 5. 2023 v 19:08 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:
On Wed, May 10, 2023 at 12:20 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:Hist 10. 5. 2023 v 17:33 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:We already have\ef\evThe use case here is simply that it saves me from:\d <table>[scroll through all the fields][often scroll right]select function name\ef [paste function name]and tab completion is much narrowerWhen doing conversions and reviews all of this stuff has to be reviewed.Oftentimes, renamed, touched.I am 100% willing to write the code, docs, etc. but would appreciate feedback.\et can be little bit confusing, because looks like editing trigger, not trigger functionwhat \eft triggername?Pavel, I am "torn" because of my OCD, I would expect\eft <TAB>to list functions that RETURN TRIGGER as opposed to the level of indirection I was aiming for.where\et <TAB>Would specifically let me complete the Trigger_Name, but find the functionIt makes me wonder, now if:\etfIs better for this (edit trigger function... given the trigger name).And as another poster suggested. As we do the AUTOCOMPLETE for that, we could address it for:\ef?because:\eft <TAB>is valuable as well, and deserves to work just like all \ef? itemsIt seems like a logical way to break it down.
This is a problem, and it isn't easy to find a design that is consistent and useful. Maybe Tom's proposal "\st" is best, although the "t" can be messy - it can be "t" like table or "t" like trigger or "t" like type.
Personally, I don't like editing DDL in psql or pgAdmin. In all my training I say "don't do it". But on second hand, I agree so it can be handy for prototyping or for some playing.
I think implementing "\st triggername" can be a good start, and then we can continue in design later.
My comments:
* Maybe "\str" can be better than only "\st". Only "\st" can be confusing - minimally we use "t" like symbol for tables
* I think so arguments can be - tablename, triggername or [tablename triggername]
It can display more triggers than just one when specification is general or result is not uniq
Regards
Pavel
regardsPavelKirk...
On Wed, May 10, 2023 at 1:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
> Hmm, I wonder how useful that's really going to be, considering
> that trigger names aren't unique across tables. Wouldn't it
> need to be more like "\et table-name trigger-name"?
Different line of thought: \et seems awfully single-purpose.
Perhaps we should think more of "\st table-name trigger-name"
(show trigger), which perhaps could print something along the
lines of
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt');
CREATE FUNCTION public.trigger_func()
RETURNS trigger
... the rest like \sf for the trigger function
If you indeed want to edit the function, it's a quick copy-and-paste
from here. But if you just want to see the trigger definition,
this is more wieldy than looking at the whole "\d table-name"
output. Also we have less of an overloading problem with the
command name.
I agree that the argument for \et or \etf fails. Simply on the one to many issues.
And I agree that a more consistent approach is best.
Having just cleaned up 158 Triggers/Trigger Functions... Just having \eft <TAB> work would be nice.
Which would solve my problem of quickly getting the tables triggers and reviewing the code.
I like the idea of adding to the \s* options. As in "show".
but the "t" is very common (table, trigger, type). I think \st \str \sty could work, but this is the first place where we would be doing this?
Honestly I think \st is "missing", especially to throw something in dbfiddle or another tool.
but the "t" is very common (table, trigger, type). I think \st \str \sty could work, but this is the first place where we would be doing this?
Honestly I think \st is "missing", especially to throw something in dbfiddle or another tool.
And if we drop "trigger" from this, then \st and \sT where T would be for Types as elsewhere.
Now that feels more consistent?
So, currently thinking:
1) lets get \ef? <TAB> working
2) Discuss: \st \sT for outputting Table and Type Creation DDL...
Something is telling me that #2 (\st) might be a can of worms, since it seems so obviously "missing"?
regards, tom lane
I appreciate the feedback!