Discussion: psql \et <trigger_name> -> edit the trigger function

Hi

st 10. 5. 2023 v 17:33 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:

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.

\et can be little bit confusing, because looks like editing trigger, not
trigger function

what \eft triggername

?

regards

Pavel

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:

Hi

st 10. 5. 2023 v 17:33 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:

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.

\et can be little bit confusing, because looks like editing trigger, not
trigger function

what \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.

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:

Hi

st 10. 5. 2023 v 17:33 odesílatel Kirk Wolak <wolakk@gmail.com> napsal:

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.

\et can be little bit confusing, because looks like editing trigger, not
trigger function

what \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.

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

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.

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!