Re: BUG #5926: information schema dtd_identifier for element_types, columns, parameters views inconsistent - Mailing list pgsql-bugs
From | Susanne Ebrecht |
---|---|
Subject | Re: BUG #5926: information schema dtd_identifier for element_types, columns, parameters views inconsistent |
Date | |
Msg-id | 4DDCE812.10401@2ndQuadrant.com Whole thread Raw |
In response to | BUG #5926: information schema dtd_identifier for element_types, columns, parameters views inconsistent ("Ingmar Brouns" <swingi@gmail.com>) |
Responses |
Re: BUG #5926: information schema dtd_identifier for
element_types, columns, parameters views inconsistent
Re: BUG #5926: information schema dtd_identifier for element_types, columns, parameters views inconsistent |
List | pgsql-bugs |
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
pgsql-bugs by date: