Pretty printed trigger in psql

Started by Takahiro Itagakialmost 16 years ago7 messages

itagaki.takahiro@oss.ntt.co.jp

almost 16 years ago

1 attachment(s)

Psql shows too many parentheses when it prints triggers with WHEN clause.

postgres=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
Triggers:
mytrig AFTER UPDATE ON t1 FOR EACH ROW
WHEN ((old.c1 <> new.c1)) EXECUTE PROCEDURE myfunc()
^ ^

The attached patch eliminates unneeded parentheses by using
pg_get_triggerdef(pretty = true) in psql.

Triggers:
mytrig AFTER UPDATE ON t1 FOR EACH ROW
WHEN (old.c1 <> new.c1) EXECUTE PROCEDURE myfunc()

I think this change is harmless because we don't use
pg_get_triggerdef(pretty = true) in any programs, including pg_dump.

Is this change ok?

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Attachments:

pretty-printed-trigger_20100112.patchapplication/octet-stream; name=pretty-printed-trigger_20100112.patchDownload

diff -cprN head/src/backend/utils/adt/ruleutils.c work/src/backend/utils/adt/ruleutils.c
*** head/src/backend/utils/adt/ruleutils.c	2010-01-04 09:10:26.638773000 +0900
--- work/src/backend/utils/adt/ruleutils.c	2010-01-12 17:51:27.595666819 +0900
*************** pg_get_triggerdef_worker(Oid trigid, boo
*** 518,527 ****
  	initStringInfo(&buf);
  
  	tgname = NameStr(trigrec->tgname);
! 	appendStringInfo(&buf, "CREATE %sTRIGGER %s",
! 					 trigrec->tgisconstraint ? "CONSTRAINT " : "",
  					 quote_identifier(tgname));
- 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	if (TRIGGER_FOR_BEFORE(trigrec->tgtype))
  		appendStringInfo(&buf, "BEFORE");
--- 518,526 ----
  	initStringInfo(&buf);
  
  	tgname = NameStr(trigrec->tgname);
! 	appendStringInfo(&buf, "CREATE %sTRIGGER %s ",
! 					 trigrec->tgisconstraint ? "CONSTRAINT" : "",
  					 quote_identifier(tgname));
  
  	if (TRIGGER_FOR_BEFORE(trigrec->tgtype))
  		appendStringInfo(&buf, "BEFORE");
*************** pg_get_triggerdef_worker(Oid trigid, boo
*** 573,605 ****
  			appendStringInfo(&buf, " TRUNCATE");
  		findx++;
  	}
! 	appendStringInfo(&buf, " ON %s",
  					 generate_relation_name(trigrec->tgrelid, NIL));
- 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	if (trigrec->tgisconstraint)
  	{
  		if (OidIsValid(trigrec->tgconstrrelid))
! 		{
! 			appendStringInfo(&buf, "FROM %s",
  							 generate_relation_name(trigrec->tgconstrrelid, NIL));
- 			appendStringInfoString(&buf, pretty ? "\n    " : " ");
- 		}
  		if (!trigrec->tgdeferrable)
  			appendStringInfo(&buf, "NOT ");
  		appendStringInfo(&buf, "DEFERRABLE INITIALLY ");
  		if (trigrec->tginitdeferred)
! 			appendStringInfo(&buf, "DEFERRED");
  		else
! 			appendStringInfo(&buf, "IMMEDIATE");
! 		appendStringInfoString(&buf, pretty ? "\n    " : " ");
  	}
  
  	if (TRIGGER_FOR_ROW(trigrec->tgtype))
! 		appendStringInfo(&buf, "FOR EACH ROW");
  	else
! 		appendStringInfo(&buf, "FOR EACH STATEMENT");
! 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	/* If the trigger has a WHEN qualification, add that */
  	value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual,
--- 572,598 ----
  			appendStringInfo(&buf, " TRUNCATE");
  		findx++;
  	}
! 	appendStringInfo(&buf, " ON %s ",
  					 generate_relation_name(trigrec->tgrelid, NIL));
  
  	if (trigrec->tgisconstraint)
  	{
  		if (OidIsValid(trigrec->tgconstrrelid))
! 			appendStringInfo(&buf, "FROM %s ",
  							 generate_relation_name(trigrec->tgconstrrelid, NIL));
  		if (!trigrec->tgdeferrable)
  			appendStringInfo(&buf, "NOT ");
  		appendStringInfo(&buf, "DEFERRABLE INITIALLY ");
  		if (trigrec->tginitdeferred)
! 			appendStringInfo(&buf, "DEFERRED ");
  		else
! 			appendStringInfo(&buf, "IMMEDIATE ");
  	}
  
  	if (TRIGGER_FOR_ROW(trigrec->tgtype))
! 		appendStringInfo(&buf, "FOR EACH ROW ");
  	else
! 		appendStringInfo(&buf, "FOR EACH STATEMENT ");
  
  	/* If the trigger has a WHEN qualification, add that */
  	value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual,
*************** pg_get_triggerdef_worker(Oid trigid, boo
*** 643,654 ****
  		context.windowClause = NIL;
  		context.windowTList = NIL;
  		context.varprefix = true;
! 		context.prettyFlags = pretty ? PRETTYFLAG_PAREN | PRETTYFLAG_INDENT : 0;
  		context.indentLevel = PRETTYINDENT_STD;
  
  		get_rule_expr(qual, &context, false);
  
! 		appendStringInfo(&buf, ")%s", pretty ? "\n    " : " ");
  	}
  
  	appendStringInfo(&buf, "EXECUTE PROCEDURE %s(",
--- 636,647 ----
  		context.windowClause = NIL;
  		context.windowTList = NIL;
  		context.varprefix = true;
! 		context.prettyFlags = pretty ? PRETTYFLAG_PAREN : 0;
  		context.indentLevel = PRETTYINDENT_STD;
  
  		get_rule_expr(qual, &context, false);
  
! 		appendStringInfo(&buf, ") ");
  	}
  
  	appendStringInfo(&buf, "EXECUTE PROCEDURE %s(",
diff -cprN head/src/bin/psql/describe.c work/src/bin/psql/describe.c
*** head/src/bin/psql/describe.c	2010-01-04 09:10:26.638773000 +0900
--- work/src/bin/psql/describe.c	2010-01-12 17:51:27.597646243 +0900
*************** describeOneTableDetails(const char *sche
*** 1854,1863 ****
  		{
  			printfPQExpBuffer(&buf,
  							  "SELECT t.tgname, "
! 							  "pg_catalog.pg_get_triggerdef(t.oid), "
  							  "t.tgenabled\n"
  							  "FROM pg_catalog.pg_trigger t\n"
  							  "WHERE t.tgrelid = '%s' AND ",
  							  oid);
  			if (pset.sversion >= 80300)
  				appendPQExpBuffer(&buf, "t.tgconstraint = 0");
--- 1854,1864 ----
  		{
  			printfPQExpBuffer(&buf,
  							  "SELECT t.tgname, "
! 							  "pg_catalog.pg_get_triggerdef(t.oid%s), "
  							  "t.tgenabled\n"
  							  "FROM pg_catalog.pg_trigger t\n"
  							  "WHERE t.tgrelid = '%s' AND ",
+ 							  (pset.sversion >= 80500 ? ", true" : ""),
  							  oid);
  			if (pset.sversion >= 80300)
  				appendPQExpBuffer(&buf, "t.tgconstraint = 0");
diff -cprN head/src/test/regress/expected/triggers.out work/src/test/regress/expected/triggers.out
*** head/src/test/regress/expected/triggers.out	2009-11-24 10:04:57.883822000 +0900
--- work/src/test/regress/expected/triggers.out	2010-01-12 17:53:21.142635393 +0900
*************** SELECT * FROM main_table ORDER BY a, b;
*** 375,387 ****
  (8 rows)
  
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
!                 pg_get_triggerdef                 
! --------------------------------------------------
!  CREATE TRIGGER modified_a                       +
!      BEFORE UPDATE OF a ON main_table            +
!      FOR EACH ROW                                +
!      WHEN (old.a <> new.a)                       +
!      EXECUTE PROCEDURE trigger_func('modified_a')
  (1 row)
  
  SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
--- 375,383 ----
  (8 rows)
  
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
!                                                              pg_get_triggerdef                                                              
! --------------------------------------------------------------------------------------------------------------------------------------------
!  CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE PROCEDURE trigger_func('modified_a')
  (1 row)
  
  SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
*************** SELECT pg_get_triggerdef(oid, false) FRO
*** 391,403 ****
  (1 row)
  
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
!                  pg_get_triggerdef                  
! ----------------------------------------------------
!  CREATE TRIGGER modified_any                       +
!      BEFORE UPDATE OF a ON main_table              +
!      FOR EACH ROW                                  +
!      WHEN (old.* IS DISTINCT FROM new.*)           +
!      EXECUTE PROCEDURE trigger_func('modified_any')
  (1 row)
  
  DROP TRIGGER modified_a ON main_table;
--- 387,395 ----
  (1 row)
  
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
!                                                                       pg_get_triggerdef                                                                       
! --------------------------------------------------------------------------------------------------------------------------------------------------------------
!  CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE PROCEDURE trigger_func('modified_any')
  (1 row)
  
  DROP TRIGGER modified_a ON main_table;
*************** SELECT pg_get_triggerdef(oid) FROM pg_tr
*** 424,438 ****
   CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row')
  (1 row)
  
- SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
-                     pg_get_triggerdef                    
- ---------------------------------------------------------
-  CREATE TRIGGER after_upd_a_b_row_trig                  +
-      AFTER UPDATE OF a, b ON main_table                 +
-      FOR EACH ROW                                       +
-      EXECUTE PROCEDURE trigger_func('after_upd_a_b_row')
- (1 row)
- 
  UPDATE main_table SET a = 50;
  NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
  NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
--- 416,421 ----
diff -cprN head/src/test/regress/sql/triggers.sql work/src/test/regress/sql/triggers.sql
*** head/src/test/regress/sql/triggers.sql	2009-11-24 10:04:57.883822000 +0900
--- work/src/test/regress/sql/triggers.sql	2010-01-12 17:51:27.597646243 +0900
*************** CREATE TRIGGER after_upd_b_stmt_trig AFT
*** 304,310 ****
  FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');
  
  SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
- SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
  
  UPDATE main_table SET a = 50;
  UPDATE main_table SET b = 10;
--- 304,309 ----

Tom Lane

tgl@sss.pgh.pa.us

almost 16 years ago

In reply to: Takahiro Itagaki (#1)

Re: Pretty printed trigger in psql

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> writes:

The attached patch eliminates unneeded parentheses by using
pg_get_triggerdef(pretty = true) in psql.

Is this patch reversed? It seems so but the listed file timestamps
don't match that idea ...

regards, tom lane

Takahiro Itagaki

itagaki.takahiro@oss.ntt.co.jp

almost 16 years ago

In reply to: Tom Lane (#2)

Re: Pretty printed trigger in psql

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> writes:

The attached patch eliminates unneeded parentheses by using
pg_get_triggerdef(pretty = true) in psql.

Is this patch reversed? It seems so but the listed file timestamps
don't match that idea ...

Sorry, I cannot understand what you mean...

My English might be broken. May I explain what I did again?

1. psql has been used pg_get_triggerdef(oid).

2. I added pg_get_triggerdef(oid, pretty = false) at the last commit fest
for pg_dump to support dumping triggeres with WHEN cluase. In that time,
PRETTYFLAG_PAREN and PRETTYFLAG_INDENT are used when pretty = true.

3 psql still uses pg_get_triggerdef(oid [, pretty=false] ).
Also, pg_dump should use (pretty=false) for safer migration.
No programs use pg_get_triggerdef(pretty=true) is for now.

4. psql will be better to use pg_get_triggerdef(oid, true) to display
trigger definitions cleanly, but it also should print them in one line.
For the purpose, the patch changes two things:
- Modify psql to use pg_get_triggerdef(oid, true) when server version >= 8.5.
- Remove PRETTYFLAG_INDENT from pg_get_triggerdef(). It will partially
revert the changes in 2.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Tom Lane

tgl@sss.pgh.pa.us

almost 16 years ago

In reply to: Takahiro Itagaki (#3)

Re: Pretty printed trigger in psql

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Is this patch reversed? It seems so but the listed file timestamps
don't match that idea ...

Sorry, I cannot understand what you mean...

The patch looks like it removes, rather than adds, your intended
changes.

regards, tom lane

Brad T. Sliger

brad@sliger.org

almost 16 years ago

In reply to: Takahiro Itagaki (#1)

2 attachment(s)

Re: Pretty printed trigger in psql

On Tuesday 12 January 2010 01:06:22 Takahiro Itagaki wrote:

Psql shows too many parentheses when it prints triggers with WHEN clause.

postgres=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
Triggers:
mytrig AFTER UPDATE ON t1 FOR EACH ROW
WHEN ((old.c1 <> new.c1)) EXECUTE PROCEDURE myfunc()
^ ^

The attached patch eliminates unneeded parentheses by using
pg_get_triggerdef(pretty = true) in psql.

Triggers:
mytrig AFTER UPDATE ON t1 FOR EACH ROW
WHEN (old.c1 <> new.c1) EXECUTE PROCEDURE myfunc()

<snip>

Greetings,

I tried to apply this patch to the latest version of PostgreSQL in git (bbfc96e). Some of the patch did not apply. Please
find attached the output from patch. The full path of the ruleutils.c.rej is src/backend/utils/adt/ruleutils.c.rej

Thanks,

--bts

Attachments:

patch.outputtext/plain; charset="iso 8859-15"; name=patch.outputDownload

ruleutils.c.rejtext/x-diff; charset="iso 8859-15"; name=ruleutils.c.rejDownload

***************
*** 518,527 ****
  	initStringInfo(&buf);
  
  	tgname = NameStr(trigrec->tgname);
! 	appendStringInfo(&buf, "CREATE %sTRIGGER %s",
! 					 trigrec->tgisconstraint ? "CONSTRAINT " : "",
  					 quote_identifier(tgname));
- 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	if (TRIGGER_FOR_BEFORE(trigrec->tgtype))
  		appendStringInfo(&buf, "BEFORE");
--- 518,526 ----
  	initStringInfo(&buf);
  
  	tgname = NameStr(trigrec->tgname);
! 	appendStringInfo(&buf, "CREATE %sTRIGGER %s ",
! 					 trigrec->tgisconstraint ? "CONSTRAINT" : "",
  					 quote_identifier(tgname));
  
  	if (TRIGGER_FOR_BEFORE(trigrec->tgtype))
  		appendStringInfo(&buf, "BEFORE");
***************
*** 573,605 ****
  			appendStringInfo(&buf, " TRUNCATE");
  		findx++;
  	}
! 	appendStringInfo(&buf, " ON %s",
  					 generate_relation_name(trigrec->tgrelid, NIL));
- 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	if (trigrec->tgisconstraint)
  	{
  		if (OidIsValid(trigrec->tgconstrrelid))
! 		{
! 			appendStringInfo(&buf, "FROM %s",
  							 generate_relation_name(trigrec->tgconstrrelid, NIL));
- 			appendStringInfoString(&buf, pretty ? "\n    " : " ");
- 		}
  		if (!trigrec->tgdeferrable)
  			appendStringInfo(&buf, "NOT ");
  		appendStringInfo(&buf, "DEFERRABLE INITIALLY ");
  		if (trigrec->tginitdeferred)
! 			appendStringInfo(&buf, "DEFERRED");
  		else
! 			appendStringInfo(&buf, "IMMEDIATE");
! 		appendStringInfoString(&buf, pretty ? "\n    " : " ");
  	}
  
  	if (TRIGGER_FOR_ROW(trigrec->tgtype))
! 		appendStringInfo(&buf, "FOR EACH ROW");
  	else
! 		appendStringInfo(&buf, "FOR EACH STATEMENT");
! 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	/* If the trigger has a WHEN qualification, add that */
  	value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual,
--- 572,598 ----
  			appendStringInfo(&buf, " TRUNCATE");
  		findx++;
  	}
! 	appendStringInfo(&buf, " ON %s ",
  					 generate_relation_name(trigrec->tgrelid, NIL));
  
  	if (trigrec->tgisconstraint)
  	{
  		if (OidIsValid(trigrec->tgconstrrelid))
! 			appendStringInfo(&buf, "FROM %s ",
  							 generate_relation_name(trigrec->tgconstrrelid, NIL));
  		if (!trigrec->tgdeferrable)
  			appendStringInfo(&buf, "NOT ");
  		appendStringInfo(&buf, "DEFERRABLE INITIALLY ");
  		if (trigrec->tginitdeferred)
! 			appendStringInfo(&buf, "DEFERRED ");
  		else
! 			appendStringInfo(&buf, "IMMEDIATE ");
  	}
  
  	if (TRIGGER_FOR_ROW(trigrec->tgtype))
! 		appendStringInfo(&buf, "FOR EACH ROW ");
  	else
! 		appendStringInfo(&buf, "FOR EACH STATEMENT ");
  
  	/* If the trigger has a WHEN qualification, add that */
  	value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual,

Takahiro Itagaki

itagaki.takahiro@oss.ntt.co.jp

almost 16 years ago

In reply to: Brad T. Sliger (#5)

1 attachment(s)

Re: Pretty printed trigger in psql

"Brad T. Sliger" <brad@sliger.org> wrote:

I tried to apply this patch to the latest version of PostgreSQL in git
(bbfc96e). Some of the patch did not apply. Please find attached the
output from patch. The full path of the ruleutils.c.rej is
src/backend/utils/adt/ruleutils.c.rej

The attached patch is rebased to current CVS.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Attachments:

pretty-printed-trigger_20100119.patchapplication/octet-stream; name=pretty-printed-trigger_20100119.patchDownload

diff -cprN head/src/backend/utils/adt/ruleutils.c work/src/backend/utils/adt/ruleutils.c
*** head/src/backend/utils/adt/ruleutils.c	2010-01-18 09:26:23.915594000 +0900
--- work/src/backend/utils/adt/ruleutils.c	2010-01-19 09:35:29.995725872 +0900
*************** pg_get_triggerdef_worker(Oid trigid, boo
*** 518,527 ****
  	initStringInfo(&buf);
  
  	tgname = NameStr(trigrec->tgname);
! 	appendStringInfo(&buf, "CREATE %sTRIGGER %s",
  					 OidIsValid(trigrec->tgconstraint) ? "CONSTRAINT " : "",
  					 quote_identifier(tgname));
- 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	if (TRIGGER_FOR_BEFORE(trigrec->tgtype))
  		appendStringInfo(&buf, "BEFORE");
--- 518,526 ----
  	initStringInfo(&buf);
  
  	tgname = NameStr(trigrec->tgname);
! 	appendStringInfo(&buf, "CREATE %sTRIGGER %s ",
  					 OidIsValid(trigrec->tgconstraint) ? "CONSTRAINT " : "",
  					 quote_identifier(tgname));
  
  	if (TRIGGER_FOR_BEFORE(trigrec->tgtype))
  		appendStringInfo(&buf, "BEFORE");
*************** pg_get_triggerdef_worker(Oid trigid, boo
*** 573,605 ****
  			appendStringInfo(&buf, " TRUNCATE");
  		findx++;
  	}
! 	appendStringInfo(&buf, " ON %s",
  					 generate_relation_name(trigrec->tgrelid, NIL));
- 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	if (OidIsValid(trigrec->tgconstraint))
  	{
  		if (OidIsValid(trigrec->tgconstrrelid))
! 		{
! 			appendStringInfo(&buf, "FROM %s",
  							 generate_relation_name(trigrec->tgconstrrelid, NIL));
- 			appendStringInfoString(&buf, pretty ? "\n    " : " ");
- 		}
  		if (!trigrec->tgdeferrable)
  			appendStringInfo(&buf, "NOT ");
  		appendStringInfo(&buf, "DEFERRABLE INITIALLY ");
  		if (trigrec->tginitdeferred)
! 			appendStringInfo(&buf, "DEFERRED");
  		else
! 			appendStringInfo(&buf, "IMMEDIATE");
! 		appendStringInfoString(&buf, pretty ? "\n    " : " ");
  	}
  
  	if (TRIGGER_FOR_ROW(trigrec->tgtype))
! 		appendStringInfo(&buf, "FOR EACH ROW");
  	else
! 		appendStringInfo(&buf, "FOR EACH STATEMENT");
! 	appendStringInfoString(&buf, pretty ? "\n    " : " ");
  
  	/* If the trigger has a WHEN qualification, add that */
  	value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual,
--- 572,598 ----
  			appendStringInfo(&buf, " TRUNCATE");
  		findx++;
  	}
! 	appendStringInfo(&buf, " ON %s ",
  					 generate_relation_name(trigrec->tgrelid, NIL));
  
  	if (OidIsValid(trigrec->tgconstraint))
  	{
  		if (OidIsValid(trigrec->tgconstrrelid))
! 			appendStringInfo(&buf, "FROM %s ",
  							 generate_relation_name(trigrec->tgconstrrelid, NIL));
  		if (!trigrec->tgdeferrable)
  			appendStringInfo(&buf, "NOT ");
  		appendStringInfo(&buf, "DEFERRABLE INITIALLY ");
  		if (trigrec->tginitdeferred)
! 			appendStringInfo(&buf, "DEFERRED ");
  		else
! 			appendStringInfo(&buf, "IMMEDIATE ");
  	}
  
  	if (TRIGGER_FOR_ROW(trigrec->tgtype))
! 		appendStringInfo(&buf, "FOR EACH ROW ");
  	else
! 		appendStringInfo(&buf, "FOR EACH STATEMENT ");
  
  	/* If the trigger has a WHEN qualification, add that */
  	value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual,
*************** pg_get_triggerdef_worker(Oid trigid, boo
*** 643,654 ****
  		context.windowClause = NIL;
  		context.windowTList = NIL;
  		context.varprefix = true;
! 		context.prettyFlags = pretty ? PRETTYFLAG_PAREN | PRETTYFLAG_INDENT : 0;
  		context.indentLevel = PRETTYINDENT_STD;
  
  		get_rule_expr(qual, &context, false);
  
! 		appendStringInfo(&buf, ")%s", pretty ? "\n    " : " ");
  	}
  
  	appendStringInfo(&buf, "EXECUTE PROCEDURE %s(",
--- 636,647 ----
  		context.windowClause = NIL;
  		context.windowTList = NIL;
  		context.varprefix = true;
! 		context.prettyFlags = pretty ? PRETTYFLAG_PAREN : 0;
  		context.indentLevel = PRETTYINDENT_STD;
  
  		get_rule_expr(qual, &context, false);
  
! 		appendStringInfo(&buf, ") ");
  	}
  
  	appendStringInfo(&buf, "EXECUTE PROCEDURE %s(",
diff -cprN head/src/bin/psql/describe.c work/src/bin/psql/describe.c
*** head/src/bin/psql/describe.c	2010-01-18 09:26:23.915594000 +0900
--- work/src/bin/psql/describe.c	2010-01-19 09:29:55.432814944 +0900
*************** describeOneTableDetails(const char *sche
*** 1854,1863 ****
  		{
  			printfPQExpBuffer(&buf,
  							  "SELECT t.tgname, "
! 							  "pg_catalog.pg_get_triggerdef(t.oid), "
  							  "t.tgenabled\n"
  							  "FROM pg_catalog.pg_trigger t\n"
  							  "WHERE t.tgrelid = '%s' AND ",
  							  oid);
  			if (pset.sversion >= 80500)
  				appendPQExpBuffer(&buf, "NOT t.tgisinternal");
--- 1854,1864 ----
  		{
  			printfPQExpBuffer(&buf,
  							  "SELECT t.tgname, "
! 							  "pg_catalog.pg_get_triggerdef(t.oid%s), "
  							  "t.tgenabled\n"
  							  "FROM pg_catalog.pg_trigger t\n"
  							  "WHERE t.tgrelid = '%s' AND ",
+ 							  (pset.sversion >= 80500 ? ", true" : ""),
  							  oid);
  			if (pset.sversion >= 80500)
  				appendPQExpBuffer(&buf, "NOT t.tgisinternal");
diff -cprN head/src/test/regress/expected/triggers.out work/src/test/regress/expected/triggers.out
*** head/src/test/regress/expected/triggers.out	2009-11-24 10:04:57.883822000 +0900
--- work/src/test/regress/expected/triggers.out	2010-01-19 09:29:55.433814774 +0900
*************** SELECT * FROM main_table ORDER BY a, b;
*** 375,387 ****
  (8 rows)
  
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
!                 pg_get_triggerdef                 
! --------------------------------------------------
!  CREATE TRIGGER modified_a                       +
!      BEFORE UPDATE OF a ON main_table            +
!      FOR EACH ROW                                +
!      WHEN (old.a <> new.a)                       +
!      EXECUTE PROCEDURE trigger_func('modified_a')
  (1 row)
  
  SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
--- 375,383 ----
  (8 rows)
  
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
!                                                              pg_get_triggerdef                                                              
! --------------------------------------------------------------------------------------------------------------------------------------------
!  CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE PROCEDURE trigger_func('modified_a')
  (1 row)
  
  SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
*************** SELECT pg_get_triggerdef(oid, false) FRO
*** 391,403 ****
  (1 row)
  
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
!                  pg_get_triggerdef                  
! ----------------------------------------------------
!  CREATE TRIGGER modified_any                       +
!      BEFORE UPDATE OF a ON main_table              +
!      FOR EACH ROW                                  +
!      WHEN (old.* IS DISTINCT FROM new.*)           +
!      EXECUTE PROCEDURE trigger_func('modified_any')
  (1 row)
  
  DROP TRIGGER modified_a ON main_table;
--- 387,395 ----
  (1 row)
  
  SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
!                                                                       pg_get_triggerdef                                                                       
! --------------------------------------------------------------------------------------------------------------------------------------------------------------
!  CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE PROCEDURE trigger_func('modified_any')
  (1 row)
  
  DROP TRIGGER modified_a ON main_table;
*************** SELECT pg_get_triggerdef(oid) FROM pg_tr
*** 424,438 ****
   CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row')
  (1 row)
  
- SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
-                     pg_get_triggerdef                    
- ---------------------------------------------------------
-  CREATE TRIGGER after_upd_a_b_row_trig                  +
-      AFTER UPDATE OF a, b ON main_table                 +
-      FOR EACH ROW                                       +
-      EXECUTE PROCEDURE trigger_func('after_upd_a_b_row')
- (1 row)
- 
  UPDATE main_table SET a = 50;
  NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
  NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
--- 416,421 ----
diff -cprN head/src/test/regress/sql/triggers.sql work/src/test/regress/sql/triggers.sql
*** head/src/test/regress/sql/triggers.sql	2009-11-24 10:04:57.883822000 +0900
--- work/src/test/regress/sql/triggers.sql	2010-01-19 09:29:55.433814774 +0900
*************** CREATE TRIGGER after_upd_b_stmt_trig AFT
*** 304,310 ****
  FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');
  
  SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
- SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
  
  UPDATE main_table SET a = 50;
  UPDATE main_table SET b = 10;
--- 304,309 ----

Brad T. Sliger

brad@sliger.org

almost 16 years ago

In reply to: Takahiro Itagaki (#6)

Re: Pretty printed trigger in psql

On Monday 18 January 2010 16:40:07 Takahiro Itagaki wrote:

"Brad T. Sliger" <brad@sliger.org> wrote:

I tried to apply this patch to the latest version of PostgreSQL in git
(bbfc96e). Some of the patch did not apply. Please find attached the
output from patch. The full path of the ruleutils.c.rej is
src/backend/utils/adt/ruleutils.c.rej

The attached patch is rebased to current CVS.

That patch applies, builds and installs. `gmake check` and `gmake distcheck` pass. The code style looks fine and the
patch doesn't seem to add additional lint.

The patch does remove extra ()'s from trigger descriptions with \d in psql.

As far as I can tell, everything looks reasonable.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Thanks,

--bts