Postgres Pro
Downloads
Home   >   mailing lists

Thread: Where are user-defined types stored/viewed

Where are user-defined types stored/viewed

From
Date:
After I execute a command like

CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer,
        employee_pk integer,
        hourly_dollars double precision,
        annual_dollars double precision);

where does this definition get stored, and what query can I run to get
teh definition back as output?

I don't see the new type show up anywhere in pgAdminII.

The new type DOES show up in the result when I run

select * from pg_type order by typname

but I see only the name itself (and a lot of "*id" columns that probably
reference something relevant), but I don't know how to get the actual
definition back so that I can use an existing type definition as the
basis for modification when application design changes are required.

~Berend Tober

Re: Where are user-defined types stored/viewed

From
Richard Huxton
Date:
On Monday 29 September 2003 13:35, btober@seaworthysys.com wrote:
> After I execute a command like
>
> CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer,
>         employee_pk integer,
>         hourly_dollars double precision,
>         annual_dollars double precision);
>
> where does this definition get stored, and what query can I run to get
> teh definition back as output?
>
> I don't see the new type show up anywhere in pgAdminII.

In psql:
\d emplokee_wage_journal_sum

If you start psql with -E it will show you the query it uses to show this too.

--
  Richard Huxton
  Archonet Ltd

Re: Where are user-defined types stored/viewed

From
Tom Lane
Date:
<btober@seaworthysys.com> writes:
> After I execute a command like
> CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer,
>         employee_pk integer,
>         hourly_dollars double precision,
>         annual_dollars double precision);

> I don't see the new type show up anywhere in pgAdminII.

Probably not.  Stand-alone composite types (like the above) are a new
feature in 7.3, and pgAdminII likely doesn't know about them.

You might try pgAdmin III, which just got out of beta I believe.

> but I see only the name itself (and a lot of "*id" columns that probably
> reference something relevant), but I don't know how to get the actual
> definition back so that I can use an existing type definition as the
> basis for modification when application design changes are required.

There's always "pg_dump -s" to extract such stuff.

            regards, tom lane

Re: Where are user-defined types stored/viewed

From
Date:
> On Monday 29 September 2003 13:35, btober@seaworthysys.com wrote:
>> After I execute a command like
>>
>> CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer,
>>         employee_pk integer,
>>         hourly_dollars double precision,
>>         annual_dollars double precision);
>>
>> where does this definition get stored, and what query can I run to
>> get teh definition back as output?
>>
>> I don't see the new type show up anywhere in pgAdminII.
>
> In psql:
> \d emplokee_wage_journal_sum
>
> If you start psql with -E it will show you the query it uses to show
> this too.
>
> --
>   Richard Huxton
>   Archonet Ltd

Based on the output produced using your advice, I came up the the
following query to help me list my type definitions:

CREATE VIEW public.complex_types AS
SELECT
  n.nspname,
  c.relname,
  a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  a.attnotnull,
  a.atthasdef,
  a.attnum,
  nspacl
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.relfilenode
WHERE pg_catalog.pg_table_is_visible(c.oid)
      AND c.relkind = 'c'
ORDER BY
    n.nspname,
    c.relname,
    a.attnum


~Berend Tober