Обсуждение: strange type name in information_schema
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
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
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 >
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
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
>
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 >> > > >
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 ?usually yes - this is older method for marking some type as array. Now
> for example float[] would then be _float right ?
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
>> >
>
>
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 >> >> > >> > >> > > >
I'll try that .
Thank you very much for your help.
Best Regards
Dan S
Thank you very much for your help.
Best Regards
Dan S
2011/5/21 Pavel Stehule <pavel.stehule@gmail.com>
> Is there any examples of how to join the system tables to get the sameyou can try to run "psql" consolewoth parameter -E, then you can see
> information as I was trying to get from the function ?
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
>> >> >
>> >
>> >
>
>