On 14.09.23 10:20, Peter Eisentraut wrote:
On 06.09.23 19:52, Alvaro Herrera wrote:
+ SELECT current_database()::information_schema.sql_identifier AS
constraint_catalog,
+ rs.nspname::information_schema.sql_identifier AS
constraint_schema,
+ con.conname::information_schema.sql_identifier AS
constraint_name,
+ format('CHECK (%s IS NOT NULL)',
at.attname)::information_schema.character_data AS check_clause
Small correction here: This should be
pg_catalog.format('%s IS NOT NULL',
at.attname)::information_schema.character_data AS check_clause
That is, the word "CHECK" and the parentheses should not be part of the
produced value.
Slightly related, so let's just tack it on here:
While testing this, I noticed that the way the check_clause of regular
check constraints is computed appears to be suboptimal. It currently does
CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
which ends up with an extra set of parentheses, which is ignorable, but
it also leaves in suffixes like "NOT VALID", which don't belong into
that column. Earlier in this thread I had contemplated a fix for the
first issue, but that wouldn't address the second issue. I think we can
fix this quite simply by using pg_get_expr() instead. I don't know why
it wasn't done like that to begin with, maybe it was just a (my?)
mistake. See attached patch.
From 4483d1f5c6c6aac047f12a36cc4a9e69d4e912f6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 19 Sep 2023 08:46:47 +0200
Subject: [PATCH] Simplify information schema check constraint deparsing
The computation of the column
information_schema.check_constraints.check_clause used
pg_get_constraintdef() plus some string manipulation to get the check
clause back out. This ended up with an extra pair of parentheses,
which is only an aesthetic problem, but also with suffixes like "NOT
VALID", which don't belong into that column. We can fix both of these
problems and simplify the code by just using pg_get_expr() instead.
---
src/backend/catalog/information_schema.sql | 3 +--
1 file changed, 1 insertion(+), 2 deletions(-)
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index 7f7de91cc2..10b34c3c5b 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -435,8 +435,7 @@ CREATE VIEW check_constraints AS
SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
CAST(rs.nspname AS sql_identifier) AS constraint_schema,
CAST(con.conname AS sql_identifier) AS constraint_name,
- CAST(substring(pg_get_constraintdef(con.oid) from 7) AS
character_data)
- AS check_clause
+ CAST(pg_get_expr(con.conbin, coalesce(c.oid, 0)) AS character_data)
AS check_clause
FROM pg_constraint con
LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
--
2.42.0
