Hi,
I have been wondering why there is no meta-command for listing
constraints in psql. So, I created a POC patch by using my
experience developing \dX command in PG14.
This feature is helpful for DBAs when they want to check or
modify the definition of constraints.
The current status of the POC patch is as follows:
- Add "\dco" command to list constraints from pg_constraint
- Not implemented yet:
- NOT NULL constraint, and so on (based on pg_attribute)
- Tab completion
- Regression test
- Document
The following is test results (See attached test_list_con.sql)
====================================================================
postgres=# \dco
List of constsraints
Schema | Name | Definition
| Table
--------+-------------------------+---------------------------------------------------------+----------
public | t01_chk_price_check | CHECK ((price > (0)::numeric))
| t01_chk
public | t02_uniq_product_no_key | UNIQUE (product_no)
| t02_uniq
public | t03_pk1_pkey | PRIMARY KEY (product_no)
| t03_pk1
public | t03_pk2_product_no_key | UNIQUE (product_no)
| t03_pk2
public | t04_fk_pkey | PRIMARY KEY (order_id)
| t04_fk
public | t04_fk_product_no_fkey | FOREIGN KEY (product_no) REFERENCES
t03_pk1(product_no) | t04_fk
public | t05_ex_c_excl | EXCLUDE USING gist (c WITH &&)
| t05_ex
(7 rows)
====================================================================
I have the following two questions that need to be discussed.
Questions:
(1) What strings should be assigned as meta-command for this feature?
Currently, \dc and \dC are not available, so I tentatively
assigned \dco. However, I do not have a strong opinion, so please
let me know if you have any suggestions.
(2) About domain constraints
There is the \dD command to show a list of domain constraints.
So I think this feature should not include it. Is it Okay?
If I can get "+1" for this new feature development, I would like to
improve the patch by adding NOT NULL constraints, and so on.
Any advice or comments would be appreciated.
Thanks,
Tatsuro Yamada
-- check
CREATE TABLE t01_chk (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
-- unique
CREATE TABLE t02_uniq (
product_no integer UNIQUE,
name text,
price numeric
);
-- primary key
CREATE TABLE t03_pk1 (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE t03_pk2 (
product_no integer UNIQUE NOT NULL,
name text,
price numeric
);
-- foreign key
CREATE TABLE t04_fk (
order_id integer PRIMARY KEY,
product_no integer REFERENCES t03_pk1 (product_no),
quantity integer
);
-- exclude
CREATE TABLE t05_ex (
c circle,
EXCLUDE USING gist (c WITH &&)
);
-- trigger
CREATE OR REPLACE FUNCTION trigger_notice_ab() RETURNS trigger as $$
begin
raise notice 'trigger % on % % % for %: (a,b)=(%,%)',
TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL,
NEW.a, NEW.b;
if TG_LEVEL = 'ROW' then
return NEW;
end if;
return null;
end;
$$ language plpgsql;
CREATE TABLE t06_trg (a int, b int);
CREATE CONSTRAINT TRIGGER t06_con_trig AFTER insert ON t06_trg DEFERRABLE
FOR each row EXECUTE PROCEDURE trigger_notice_ab();
-- domain
CREATE DOMAIN t07_posint AS integer CHECK (VALUE > 0);
CREATE TABLE t07_dm (id t07_posint);
-- not null
CREATE TABLE t08_notnull (
a varchar NOT NULL
);
-- default
CREATE TABLE t09_default (
a varchar DEFAULT 'a'
);
-- Tests
\dco
\dco t01_chk_price_check
\dco t02*
\dcoS pg_stat*
-- Visibility tests
---- visible
create schema con_s1;
create TABLE con_s1.t10_test (a int PRIMARY KEY);
set search_path to public, con_s1;
\dco
---- not visible
create role regress_constraint nosuperuser;
set role regress_constraint;
\dco
reset role;
-- clean-up
DROP TABLE t01_chk,
t02_uniq,
t03_pk1,
t03_pk2,
t04_fk,
t05_ex,
t06_trg,
t07_dm,
t08_notnull,
t09_default,
con_s1.t10_test;
drop domain t07_posint;
drop schema con_s1;
drop role regress_constraint;
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 49d4c0e3ce..c450972f27 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -769,7 +769,10 @@ exec_command_d(PsqlScanState scan_state, bool
active_branch, const char *cmd)
success = describeTablespaces(pattern,
show_verbose);
break;
case 'c':
- success = listConversions(pattern,
show_verbose, show_system);
+ if (strncmp(cmd, "dco", 3) == 0 || strncmp(cmd,
"dcoS", 4) == 0) /* Constraint */
+ success = listConstraints(pattern,
show_system);
+ else
+ success = listConversions(pattern,
show_verbose, show_system); /* Conversion */
break;
case 'C':
success = listCasts(pattern, show_verbose);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ea721d963a..bd1ec2df5f 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4815,6 +4815,61 @@ listExtendedStats(const char *pattern)
return true;
}
+/*
+ * \dco
+ *
+ * Describes constraints
+ */
+bool
+listConstraints(const char *pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT \n"
+ "n.nspname AS \"%s\", \n"
+ "cst.conname AS \"%s\", \n"
+
"pg_catalog.pg_get_constraintdef(cst.oid) AS \"%s\", \n"
+ "c.relname AS \"%s\" \n"
+ "FROM pg_constraint cst \n"
+ "JOIN pg_namespace n ON n.oid =
cst.connamespace \n"
+ "JOIN pg_class c ON c.oid =
cst.conrelid \n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Definition"),
+ gettext_noop("Table")
+ );
+
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf,
+ "WHERE n.nspname <>
'pg_catalog' \n"
+ " AND n.nspname <>
'information_schema' \n");
+
+ processSQLNamePattern(pset.db, &buf, pattern,
+ !showSystem && !pattern,
false,
+ "n.nspname", "cst.conname",
+ NULL,
"pg_catalog.pg_table_is_visible(cst.conrelid)");
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of constsraints");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
/*
* \dC
*
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 71b320f1fc..53e51db036 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -76,6 +76,9 @@ extern bool listDomains(const char *pattern, bool verbose,
bool showSystem);
/* \dc */
extern bool listConversions(const char *pattern, bool verbose, bool
showSystem);
+/* \dco */
+extern bool listConstraints(const char *pattern, bool showSystem);
+
/* \dC */
extern bool listCasts(const char *pattern, bool verbose);
