Skip site navigation (1) Skip section navigation (2)

Bug in CHECK constraints statement reverse engineering.

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 (view raw or flat)
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


pgadmin-support by date

Next:From: Dave PageDate: 2005-05-20 08:30:34
Subject: Re: Bug in CHECK constraints statement reverse engineering.
Previous:From: Sim ZacksDate: 2005-05-15 06:11:32
Subject: Re: Fwd: [NOVICE] Autocommit in Postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group