Re: array of domain types

On 03.06.2016 02:02, Rod Taylor wrote:
>
>
> On Thu, Jun 2, 2016 at 10:42 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>> wrote:
>
> On 02.06.2016 17:22, Tom Lane wrote:
>
> konstantin knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru
> <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>> 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