Re: array of domain types - Mailing list pgsql-hackers

On 03.06.2016 02:02, Rod Taylor wrote:

On Thu, Jun 2, 2016 at 10:42 AM, Konstantin Knizhnik <[email protected]> wrote:

On 02.06.2016 17:22, Tom Lane wrote:

konstantin knizhnik <[email protected]> writes:

Attached please find patch for DefineDomain function.

You didn't attach the patch,

Sorry, but I did attached the patch - I see the attachment in my mail received from the group.
Multidimensional arrays work fine:

knizhnik=# SELECT '{{14},{20}}'::teenager[][];
ERROR:  value for domain teenager violates check constraint "teenager_check"
LINE 1: SELECT '{{14},{20}}'::teenager[][];
               ^
knizhnik=# SELECT '{{14},{19}}'::teenager[][];
  teenager
-------------
 {{14},{19}}
(1 row)

knizhnik=# SELECT ('{{14},{19}}'::teenager[][])[1][1];
 teenager
----------
       14
(1 row)


Domain of array of domain also works:

I applied the domain.patch from above on HEAD, and all I get is cache lookup failures. The type_sanity regression test fails too.

postgres=# CREATE DOMAIN teenager AS int CHECK (VALUE BETWEEN 13 AND 20);
CREATE DOMAIN
postgres=# CREATE DOMAIN teenager_groups AS teenager[];
CREATE DOMAIN
postgres=# CREATE TABLE x (col teenager_groups);
ERROR:  cache lookup failed for type 0

Anyway, if that worked for me I would have done this which I expect will succeed when it shouldn't.

INSERT INTO x VALUES (ARRAY[13,14,20]);
ALTER DOMAIN teenager DROP CONSTRAINT teenager_check;
ALTER DOMAIN teenager ADD CHECK (VALUE BETWEEN 13 AND 19);

Sorry, the problem is more difficult than I originally expected:(
Attached patch passes all regression tests and correctly handle conversion of arrays.
But constraints are not checked for table columns. I failed to locate place where this check should be inserted...
 
Originally I was mostly interested in domains as kind of typedefs: convenient way to assign type to some particular kind of columns,
for example object reference used in ORM. 
There are two main goals of using domain here:
1. Be able to easily change representation of object identifier, for example from integer to bigint.
2. Detect all columns containing references (distinguish them from columns containing just normal integers).
I do not see any other mechanism in PostgreSQL which can address this problem (for example user defined type can not help here).

I wonder if it is possible to support arrays of domain which do not have constraints?
Or such partial support is worser than prohibiting arrays of domains at all?

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company