Обсуждение: strange type name in information_schema

Поиск
Список
Период
Сортировка

strange type name in information_schema

От
Dan S
Дата:
Hi !

I'm running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500, 32-bit".

I'm trying to get type information on functions out of information_schema.
When there is an array as input or output of a function I try to query information_schema of the array type.

In this case udt_name gives the type name _text why does it not give text ?

Is this the right way to query the parameter types ?

CREATE OR REPLACE FUNCTION test(ta text[]) RETURNS void AS $$
BEGIN
RETURN;
END;
$$ LANGUAGE plpgsql;

select p.udt_name,p.data_type,*
from information_schema.routines r ,information_schema.parameters p
where r.routine_name = 'test'
and p.specific_name = r.specific_name
and p.specific_catalog=r.specific_catalog
and p.specific_schema=r.specific_schema


Best Regards
Dan S

Re: strange type name in information_schema

От
Pavel Stehule
Дата:
Hello

type "array of text" has name "_text"

Regards

Pavel Stehule

2011/5/21 Dan S <strd911@gmail.com>:
> Hi !
>
> I'm running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500, 32-bit".
>
> I'm trying to get type information on functions out of information_schema.
> When there is an array as input or output of a function I try to query
> information_schema of the array type.
>
> In this case udt_name gives the type name _text why does it not give text ?
>
> Is this the right way to query the parameter types ?
>
> CREATE OR REPLACE FUNCTION test(ta text[]) RETURNS void AS $$
> BEGIN
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> select p.udt_name,p.data_type,*
> from information_schema.routines r ,information_schema.parameters p
> where r.routine_name = 'test'
> and p.specific_name = r.specific_name
> and p.specific_catalog=r.specific_catalog
> and p.specific_schema=r.specific_schema
>
>
> Best Regards
> Dan S
>

Re: strange type name in information_schema

От
Dan S
Дата:
So is there always an underscore prepended to the type name of an array ?
for example float[] would then be _float right ?

Best Regards
Dan S

2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
Hello

type "array of text" has name "_text"

Regards

Pavel Stehule

2011/5/21 Dan S <strd911@gmail.com>:
> Hi !
>
> I'm running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500, 32-bit".
>
> I'm trying to get type information on functions out of information_schema.
> When there is an array as input or output of a function I try to query
> information_schema of the array type.
>
> In this case udt_name gives the type name _text why does it not give text ?
>
> Is this the right way to query the parameter types ?
>
> CREATE OR REPLACE FUNCTION test(ta text[]) RETURNS void AS $$
> BEGIN
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> select p.udt_name,p.data_type,*
> from information_schema.routines r ,information_schema.parameters p
> where r.routine_name = 'test'
> and p.specific_name = r.specific_name
> and p.specific_catalog=r.specific_catalog
> and p.specific_schema=r.specific_schema
>
>
> Best Regards
> Dan S
>

Re: strange type name in information_schema

От
Pavel Stehule
Дата:
2011/5/21 Dan S <strd911@gmail.com>:
> So is there always an underscore prepended to the type name of an array ?
> for example float[] would then be _float right ?

usually yes - this is older method for marking some type as array. Now
array types are described by typelem in pg_type table.

Pavel

>
> Best Regards
> Dan S
>
> 2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> Hello
>>
>> type "array of text" has name "_text"
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2011/5/21 Dan S <strd911@gmail.com>:
>> > Hi !
>> >
>> > I'm running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500,
>> > 32-bit".
>> >
>> > I'm trying to get type information on functions out of
>> > information_schema.
>> > When there is an array as input or output of a function I try to query
>> > information_schema of the array type.
>> >
>> > In this case udt_name gives the type name _text why does it not give
>> > text ?
>> >
>> > Is this the right way to query the parameter types ?
>> >
>> > CREATE OR REPLACE FUNCTION test(ta text[]) RETURNS void AS $$
>> > BEGIN
>> > RETURN;
>> > END;
>> > $$ LANGUAGE plpgsql;
>> >
>> > select p.udt_name,p.data_type,*
>> > from information_schema.routines r ,information_schema.parameters p
>> > where r.routine_name = 'test'
>> > and p.specific_name = r.specific_name
>> > and p.specific_catalog=r.specific_catalog
>> > and p.specific_schema=r.specific_schema
>> >
>> >
>> > Best Regards
>> > Dan S
>> >
>
>

Re: strange type name in information_schema

От
Dan S
Дата:

Is there any examples of how to join the system tables to get the same information as I was trying to get from the function ?

Best Regards
Dan S

2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
2011/5/21 Dan S <strd911@gmail.com>:
> So is there always an underscore prepended to the type name of an array ?
> for example float[] would then be _float right ?

usually yes - this is older method for marking some type as array. Now
array types are described by typelem in pg_type table.

Pavel

>
> Best Regards
> Dan S
>
> 2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> Hello
>>
>> type "array of text" has name "_text"
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2011/5/21 Dan S <strd911@gmail.com>:
>> > Hi !
>> >
>> > I'm running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500,
>> > 32-bit".
>> >
>> > I'm trying to get type information on functions out of
>> > information_schema.
>> > When there is an array as input or output of a function I try to query
>> > information_schema of the array type.
>> >
>> > In this case udt_name gives the type name _text why does it not give
>> > text ?
>> >
>> > Is this the right way to query the parameter types ?
>> >
>> > CREATE OR REPLACE FUNCTION test(ta text[]) RETURNS void AS $$
>> > BEGIN
>> > RETURN;
>> > END;
>> > $$ LANGUAGE plpgsql;
>> >
>> > select p.udt_name,p.data_type,*
>> > from information_schema.routines r ,information_schema.parameters p
>> > where r.routine_name = 'test'
>> > and p.specific_name = r.specific_name
>> > and p.specific_catalog=r.specific_catalog
>> > and p.specific_schema=r.specific_schema
>> >
>> >
>> > Best Regards
>> > Dan S
>> >
>
>

Re: strange type name in information_schema

От
Pavel Stehule
Дата:
2011/5/21 Dan S <strd911@gmail.com>:
>
> Is there any examples of how to join the system tables to get the same
> information as I was trying to get from the function ?

you can try to run "psql" consolewoth parameter -E, then you can see
all SQL to system tables

[pavel@nemesis src]$ psql -E postgres
psql (9.1beta1)
Type "help" for help.

postgres=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f'
THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | foo  | table | pavel
 public | tbl1 | table | pavel
(2 rows)

postgres=# \d foo
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(foo)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN
c.reloftype = 0 THEN '' ELSE
c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '16385'

**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
a.attcollation <> t.typcollation) AS attcollation
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16385' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
'16385' ORDER BY inhseqno
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
'16385' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************

    Table "public.foo"
 Column | Type | Modifiers
--------+------+-----------
 a      | text |

postgres=# \df
********* QUERY **********
SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
**************************

                            List of functions
 Schema |     Name      | Result data type | Argument data types |  Type
--------+---------------+------------------+---------------------+--------
 public | dynamic_query | TABLE(i integer) | i integer           | normal
 public | foo           | void             |                     | normal
(2 rows)

Regards

Pavel


>
> Best Regards
> Dan S
>
> 2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> 2011/5/21 Dan S <strd911@gmail.com>:
>> > So is there always an underscore prepended to the type name of an array
>> > ?
>> > for example float[] would then be _float right ?
>>
>> usually yes - this is older method for marking some type as array. Now
>> array types are described by typelem in pg_type table.
>>
>> Pavel
>>
>> >
>> > Best Regards
>> > Dan S
>> >
>> > 2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
>> >>
>> >> Hello
>> >>
>> >> type "array of text" has name "_text"
>> >>
>> >> Regards
>> >>
>> >> Pavel Stehule
>> >>
>> >> 2011/5/21 Dan S <strd911@gmail.com>:
>> >> > Hi !
>> >> >
>> >> > I'm running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500,
>> >> > 32-bit".
>> >> >
>> >> > I'm trying to get type information on functions out of
>> >> > information_schema.
>> >> > When there is an array as input or output of a function I try to
>> >> > query
>> >> > information_schema of the array type.
>> >> >
>> >> > In this case udt_name gives the type name _text why does it not give
>> >> > text ?
>> >> >
>> >> > Is this the right way to query the parameter types ?
>> >> >
>> >> > CREATE OR REPLACE FUNCTION test(ta text[]) RETURNS void AS $$
>> >> > BEGIN
>> >> > RETURN;
>> >> > END;
>> >> > $$ LANGUAGE plpgsql;
>> >> >
>> >> > select p.udt_name,p.data_type,*
>> >> > from information_schema.routines r ,information_schema.parameters p
>> >> > where r.routine_name = 'test'
>> >> > and p.specific_name = r.specific_name
>> >> > and p.specific_catalog=r.specific_catalog
>> >> > and p.specific_schema=r.specific_schema
>> >> >
>> >> >
>> >> > Best Regards
>> >> > Dan S
>> >> >
>> >
>> >
>
>

Re: strange type name in information_schema

От
Dan S
Дата:
I'll try that .

Thank you very much for your help.

Best Regards
Dan S

2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
2011/5/21 Dan S <strd911@gmail.com>:
>
> Is there any examples of how to join the system tables to get the same
> information as I was trying to get from the function ?

you can try to run "psql" consolewoth parameter -E, then you can see
all SQL to system tables

[pavel@nemesis src]$ psql -E postgres
psql (9.1beta1)
Type "help" for help.

postgres=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
 c.relname as "Name",
 CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f'
THEN 'foreign table' END as "Type",
 pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
     AND n.nspname <> 'pg_catalog'
     AND n.nspname <> 'information_schema'
     AND n.nspname !~ '^pg_toast'
 AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

      List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | foo  | table | pavel
 public | tbl1 | table | pavel
(2 rows)

postgres=# \d foo
********* QUERY **********
SELECT c.oid,
 n.nspname,
 c.relname
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(foo)$'
 AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN
c.reloftype = 0 THEN '' ELSE
c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '16385'

**************************

********* QUERY **********
SELECT a.attname,
 pg_catalog.format_type(a.atttypid, a.atttypmod),
 (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
  FROM pg_catalog.pg_attrdef d
  WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
 a.attnotnull, a.attnum,
 (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
  WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
a.attcollation <> t.typcollation) AS attcollation
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16385' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
'16385' ORDER BY inhseqno
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
'16385' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************

   Table "public.foo"
 Column | Type | Modifiers
--------+------+-----------
 a      | text |

postgres=# \df
********* QUERY **********
SELECT n.nspname as "Schema",
 p.proname as "Name",
 pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
 WHEN p.proisagg THEN 'agg'
 WHEN p.proiswindow THEN 'window'
 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
 ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
     AND n.nspname <> 'pg_catalog'
     AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
**************************

                           List of functions
 Schema |     Name      | Result data type | Argument data types |  Type
--------+---------------+------------------+---------------------+--------
 public | dynamic_query | TABLE(i integer) | i integer           | normal
 public | foo           | void             |                     | normal
(2 rows)

Regards

Pavel


>
> Best Regards
> Dan S
>
> 2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> 2011/5/21 Dan S <strd911@gmail.com>:
>> > So is there always an underscore prepended to the type name of an array
>> > ?
>> > for example float[] would then be _float right ?
>>
>> usually yes - this is older method for marking some type as array. Now
>> array types are described by typelem in pg_type table.
>>
>> Pavel
>>
>> >
>> > Best Regards
>> > Dan S
>> >
>> > 2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
>> >>
>> >> Hello
>> >>
>> >> type "array of text" has name "_text"
>> >>
>> >> Regards
>> >>
>> >> Pavel Stehule
>> >>
>> >> 2011/5/21 Dan S <strd911@gmail.com>:
>> >> > Hi !
>> >> >
>> >> > I'm running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500,
>> >> > 32-bit".
>> >> >
>> >> > I'm trying to get type information on functions out of
>> >> > information_schema.
>> >> > When there is an array as input or output of a function I try to
>> >> > query
>> >> > information_schema of the array type.
>> >> >
>> >> > In this case udt_name gives the type name _text why does it not give
>> >> > text ?
>> >> >
>> >> > Is this the right way to query the parameter types ?
>> >> >
>> >> > CREATE OR REPLACE FUNCTION test(ta text[]) RETURNS void AS $$
>> >> > BEGIN
>> >> > RETURN;
>> >> > END;
>> >> > $$ LANGUAGE plpgsql;
>> >> >
>> >> > select p.udt_name,p.data_type,*
>> >> > from information_schema.routines r ,information_schema.parameters p
>> >> > where r.routine_name = 'test'
>> >> > and p.specific_name = r.specific_name
>> >> > and p.specific_catalog=r.specific_catalog
>> >> > and p.specific_schema=r.specific_schema
>> >> >
>> >> >
>> >> > Best Regards
>> >> > Dan S
>> >> >
>> >
>> >
>
>