pretty print for check constraints broken

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:

Hi,

The brackets aren't put on the CHECK constraints properly.

Before patch:

test=# select pg_get_constraintdef(oid) from pg_constraint;
pg_get_constraintdef
-------------------------------------------------------------------------------------------------
CHECK (VALUE >= 0)
CHECK ((((a)::text = 'asdf'::text) OR ((a)::text = 'fdsa'::text)) OR
((a)::text = 'dfd'::text))
PRIMARY KEY (b)
FOREIGN KEY (a) REFERENCES test2(b)
UNIQUE (b)
(5 rows)

test=# select pg_get_constraintdef(oid, true) from pg_constraint;
pg_get_constraintdef
-----------------------------------------------------------------------------------
CHECK VALUE >= 0
CHECK a::text = 'asdf'::text OR a::text = 'fdsa'::text OR a::text =
'dfd'::text
PRIMARY KEY (b)
FOREIGN KEY (a) REFERENCES test2(b)
UNIQUE (b)
(5 rows)

After patch:

test=# select pg_get_constraintdef(oid) from pg_constraint;
pg_get_constraintdef
-------------------------------------------------------------------------------------------------
CHECK (VALUE >= 0)
CHECK ((((a)::text = 'asdf'::text) OR ((a)::text = 'fdsa'::text)) OR
((a)::text = 'dfd'::text))
PRIMARY KEY (b)
FOREIGN KEY (a) REFERENCES test2(b)
UNIQUE (b)
(5 rows)

test=# select pg_get_constraintdef(oid, true) from pg_constraint;
pg_get_constraintdef
-----------------------------------------------------------------------------------
CHECK (VALUE >= 0)
CHECK (a::text = 'asdf'::text OR a::text = 'fdsa'::text OR a::text =
'dfd'::text)
PRIMARY KEY (b)
FOREIGN KEY (a) REFERENCES test2(b)
UNIQUE (b)
(5 rows)

It's important that those brackets are there to (a) match all other
constraints and (b) so that people can just copy and paste them and it
will work as SQL.

Chris

Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.154
diff -c -r1.154 ruleutils.c
*** src/backend/utils/adt/ruleutils.c	15 Sep 2003 20:03:37 -0000	1.154
--- src/backend/utils/adt/ruleutils.c	26 Sep 2003 07:45:02 -0000
***************
*** 1056,1061 ****
--- 1056,1065 ----
*/
appendStringInfo(&buf, "CHECK ");

+ 				/* If we're pretty-printing we need to add brackets */
+ 				if (prettyFlags != 0)
+ 					appendStringInfo(&buf, "(");
+ 
/* Fetch constraint source */
val = heap_getattr(tup, Anum_pg_constraint_conbin,
RelationGetDescr(conDesc), &isnull);
***************
*** 1093,1098 ****
--- 1097,1106 ----

/* Append the constraint source */
appendStringInfoString(&buf, consrc);
+ 
+ 				/* If we're pretty-printing we need to add brackets */
+ 				if (prettyFlags != 0)
+ 					appendStringInfo(&buf, ")");

break;
}

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Patch applied. Thanks.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:

Hi,

The brackets aren't put on the CHECK constraints properly.

Before patch:

test=# select pg_get_constraintdef(oid) from pg_constraint;
pg_get_constraintdef
-------------------------------------------------------------------------------------------------
CHECK (VALUE >= 0)
CHECK ((((a)::text = 'asdf'::text) OR ((a)::text = 'fdsa'::text)) OR
((a)::text = 'dfd'::text))
PRIMARY KEY (b)
FOREIGN KEY (a) REFERENCES test2(b)
UNIQUE (b)
(5 rows)

test=# select pg_get_constraintdef(oid, true) from pg_constraint;
pg_get_constraintdef
-----------------------------------------------------------------------------------
CHECK VALUE >= 0
CHECK a::text = 'asdf'::text OR a::text = 'fdsa'::text OR a::text =
'dfd'::text
PRIMARY KEY (b)
FOREIGN KEY (a) REFERENCES test2(b)
UNIQUE (b)
(5 rows)

After patch:

test=# select pg_get_constraintdef(oid) from pg_constraint;
pg_get_constraintdef
-------------------------------------------------------------------------------------------------
CHECK (VALUE >= 0)
CHECK ((((a)::text = 'asdf'::text) OR ((a)::text = 'fdsa'::text)) OR
((a)::text = 'dfd'::text))
PRIMARY KEY (b)
FOREIGN KEY (a) REFERENCES test2(b)
UNIQUE (b)
(5 rows)

test=# select pg_get_constraintdef(oid, true) from pg_constraint;
pg_get_constraintdef
-----------------------------------------------------------------------------------
CHECK (VALUE >= 0)
CHECK (a::text = 'asdf'::text OR a::text = 'fdsa'::text OR a::text =
'dfd'::text)
PRIMARY KEY (b)
FOREIGN KEY (a) REFERENCES test2(b)
UNIQUE (b)
(5 rows)

It's important that those brackets are there to (a) match all other
constraints and (b) so that people can just copy and paste them and it
will work as SQL.

Chris

Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.154
diff -c -r1.154 ruleutils.c
*** src/backend/utils/adt/ruleutils.c	15 Sep 2003 20:03:37 -0000	1.154
--- src/backend/utils/adt/ruleutils.c	26 Sep 2003 07:45:02 -0000
***************
*** 1056,1061 ****
--- 1056,1065 ----
*/
appendStringInfo(&buf, "CHECK ");

+ 				/* If we're pretty-printing we need to add brackets */
+ 				if (prettyFlags != 0)
+ 					appendStringInfo(&buf, "(");
+ 
/* Fetch constraint source */
val = heap_getattr(tup, Anum_pg_constraint_conbin,
RelationGetDescr(conDesc), &isnull);
***************
*** 1093,1098 ****
--- 1097,1106 ----

/* Append the constraint source */
appendStringInfoString(&buf, consrc);
+ 
+ 				/* If we're pretty-printing we need to add brackets */
+ 				if (prettyFlags != 0)
+ 					appendStringInfo(&buf, ")");

break;
}

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073