By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,776 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,776 IT Pros & Developers. It's quick & easy.

Where are user-defined types stored/viewed

P: n/a
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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Monday 29 September 2003 13:35, bt****@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a
<bt****@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

P: n/a
On Monday 29 September 2003 13:35, bt****@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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.