Message-ID: <4DDCE812.10401@2ndQuadrant.com>
Date: Wed, 25 May 2011 13:29:22 +0200
From: Susanne Ebrecht <susanne@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.17) Gecko/20110424 Thunderbird/3.1.10
MIME-Version: 1.0
To: Ingmar Brouns <swingi@gmail.com>
CC: pgsql-bugs@postgresql.org, pgsql-docs@postgresql.org
Subject: Re: BUG #5926: information schema dtd_identifier for element_types,
	columns, parameters views inconsistent
References: <201103111318.p2BDITcL034900@wwwmaster.postgresql.org>
In-Reply-To: <201103111318.p2BDITcL034900@wwwmaster.postgresql.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 11.03.2011 14:18, Ingmar Brouns wrote:
> The following bug has been logged online:
>
> Bug reference:      5926
> Logged by:          Ingmar Brouns
> Email address:      swingi@gmail.com
> PostgreSQL version: 9.0.3
> Operating system:   Ubuntu 9.0.4
> Description:        information schema dtd_identifier for element_types,
> columns, parameters views inconsistent
> Details:
>
> Hi,
>
> I am writing a function that needs to retrieve information with respect to
> the types of parameters of functions. I use the information schema for that.
> The parameters view documentation states:
>
> data_type: Data type of the parameter, if it is a built-in type, or ARRAY if
> it is some array (in that case, see the view element_types)
>
> So for arrays I will have to join with information_schema.element_types
>
> http://www.postgresql.org/docs/9.0/static/infoschema-element-types.html
>
> At the top op that documentation is some example code, it joins on
> dtd_identifier, the code does not work:
>
> create table public.test_table(a varchar array, b integer, c integer
> array);
>
> SELECT c.column_name, c.data_type, e.data_type AS element_type
> FROM information_schema.columns c
> LEFT JOIN information_schema.element_types e
> ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE',
> c.dtd_identifier)
>     = (e.object_catalog, e.object_schema, e.object_name, e.object_type,
> e.dtd_identifier))
> WHERE c.table_schema = 'public' AND c.table_name = 'test_table'
> ORDER BY c.ordinal_position;
>
>   column_name | data_type | element_type
> -------------+-----------+--------------
>   a           | ARRAY     |
>   b           | integer   |
>   c           | ARRAY     |
> (3 rows)
>
> The same holds when joining with the parameters view. The reason seems to be
> that the dtd_identifier of the element_types view has prepended 'a's whereas
> the dtd_identifiers of the columns and parameter views do not:
>
> select column_name,dtd_identifier
> from information_schema.columns c
> where c.table_schema = 'public'
> and   c.table_name   = 'test_table';
>
>   column_name | dtd_identifier
> -------------+----------------
>   a           | 1
>   b           | 2
>   c           | 3
> (3 rows)
>
> select dtd_identifier
> from information_schema.element_types e
> where e.object_schema = 'public'
> and e.object_name = 'test_table';
>
>   dtd_identifier
> ----------------
>   a1
>   a3
> (2 rows)
>
> The element_types view has a column 'collection_type_identifier', this
> column is not present in the documentation. It is defined exactly as the
> dtd_identifier, only then without the prepended 'a':
>
> ('a'::text || x.objdtdid::text)::information_schema.sql_identifier AS
> dtd_identifier
>               x.objdtdid       ::information_schema.sql_identifier AS
> collection_type_identifier
>
> When I modify the example code to join on this column instead, I get the
> expected results:
>
> SELECT c.column_name, c.data_type, e.data_type AS element_type
> FROM information_schema.columns c LEFT JOIN information_schema.element_types
> e
>      ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE',
> c.dtd_identifier)
>        = (e.object_catalog, e.object_schema, e.object_name, e.object_type,
> e.collection_type_identifier))
> WHERE c.table_schema = 'public' AND c.table_name = 'test_table'
> ORDER BY c.ordinal_position;
>
>   column_name | data_type |   element_type
> -------------+-----------+-------------------
>   a           | ARRAY     | character varying
>   b           | integer   |
>   c           | ARRAY     | integer
> (3 rows)
>
>

Many thanks for figuring this out.

I think we should fix the documentation here.

Best Regards,

Susanne Ebrecht

-- 
Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com