Postgres Pro
Downloads
Home   >   mailing lists

Thread: Discussion: psql \et -> edit the trigger function

Discussion: psql \et -> edit the trigger function

From
Kirk Wolak
Date:
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...

Re: Discussion: psql \et -> edit the trigger function

From
Pavel Stehule
Date:
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...

Re: Discussion: psql \et -> edit the trigger function

From
Dagfinn Ilmari Mannsåker
Date:
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

Re: Discussion: psql \et -> edit the trigger function

From
Kirk Wolak
Date:
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.   
regards

Pavel

 

Kirk...

Re: Discussion: psql \et -> edit the trigger function

From
Tom Lane
Date:
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

Re: Discussion: psql \et -> edit the trigger function

From
Tom Lane
Date:
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

Re: Discussion: psql \et -> edit the trigger function

From
Pavel Stehule
Date:


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







 
regards

Pavel

 

Kirk...

Re: Discussion: psql \et -> edit the trigger function

From
Kirk Wolak
Date:
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!