Re: strange type name in information_schema - Mailing list pgsql-general

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
>> >> >
>> >
>> >
>
>