From: | Ivan <Ivan-Sun1(at)mail(dot)ru> |
---|---|
To: | pgadmin-support(at)postgresql(dot)org |
Subject: | Bug in CHECK constraints statement reverse engineering. |
Date: | 2005-05-19 15:37:13 |
Message-ID: | 04570087.20050519193713@mail.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
Hello,
PgAdmin 1.3.0 (Apr 24 2005)
Wrong CHECK reverse engineering.
In PostgreSQL documentation i found:
----------------------------------------------------------------------
41.10. pg_constraint
Note
consrc is not updated when referenced objects change; for example,
it won't track renaming of columns. Rather than relying on this field,
it's best to use pg_get_constraintdef() to extract the definition
of a check constraint.
----------------------------------------------------------------------
Here the script to demonstraint this behaviour:
-- First create function and constraints on table field and domain
CREATE OR REPLACE FUNCTION "Check_IntegerGreaterZero"(int4)
RETURNS bool AS
$BODY$
select $1 > 0;
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
CREATE TABLE "TestTable"
(
test int4,
CONSTRAINT "CHK_TestTable_Test" CHECK ("Check_IntegerGreaterZero"(test))
)
WITHOUT OIDS;
CREATE DOMAIN "TestDomain"
AS int4
CONSTRAINT TestDomain_check CHECK "Check_IntegerGreaterZero"(VALUE);
-- Then just rename function
ALTER FUNCTION "Check_IntegerGreaterZero"(int4) RENAME TO "Check_IGZ";
--Watch pg_constraint and result of pg_get_constraintdef()
select conname, consrc, pg_get_constraintdef(pg_constraint.oid) from pg_constraint
conname |consrc |pg_get_constraintdef
--------------------------------------------------------------------------------------------------------------------------------
cardinal_number_domain_check |(VALUE >= 0) |CHECK ((VALUE>= 0))
CHK_TestTable_Test |"Check_IntegerGreaterZero"(test) |CHECK("Check_IGZ"(test))
TestDomain_check |"Check_IntegerGreaterZero"(VALUE) |CHECK("Check_IGZ"(VALUE))
I suppose that PgAdmin shows values from consrc.
Another feature request. The function described above ("Check_IntegerGreaterZero") in PgAdmin
right bottom pane shows as
-------------------------------------------------------------------------------------
-- Function: Check_IntegerGreaterZero(in_Value int4)
-- DROP FUNCTION "Check_IntegerGreaterZero"(int4);
CREATE OR REPLACE FUNCTION "Check_IntegerGreaterZero"(int4)
RETURNS bool AS
$BODY$
select $1 > 0;
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION "Check_IntegerGreaterZero"(int4) OWNER TO postgres;
-------------------------------------------------------------------------------------
It will be convenient for me if the first line will be
-- Function: "Check_IntegerGreaterZero"("in_Value" int4)
- quoted function name and argument names. It is useful for copy / paste purposes :)
Thank you for support.
--
Best regards,
Ivan mailto:Ivan-Sun1(at)mail(dot)ru
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2005-05-20 08:30:34 | Re: Bug in CHECK constraints statement reverse engineering. |
Previous Message | Sim Zacks | 2005-05-15 06:11:32 | Re: Fwd: [NOVICE] Autocommit in Postgresql |