1

In my Postgres database i have this table:

CREATE TYPE address_type AS (
     name    text,
     street  text,
     houseno integer );

CREATE TABLE person (
     address   address_type,
     count     integer );

I need to fetch meta data of the fields for address_type.
I have been through the documentation and this and it's likes but getting just the column name and it's datatype is not enough for me, i need to get the meta data for the fields of the column as well, like, what all fields the struct has (name, street, houseno) and what are their types (text, text, int).
Can someone please guide me in this regard? Is it even feasible at all?

alex
  • 13
  • 3
  • Not through simple JDBC driver methods. You can probably query the `pg_*` tables to get the info. – Kayaman Sep 13 '17 at 06:43
  • Can you please share some references? I am a postgres rookie. – alex Sep 13 '17 at 06:47
  • You can find user types (and built-in types) from `pg_catalog.pg_type` and related tables. It's a bit of a read if you get into the internals, but the JDBC driver has to support a limited superset of functionality, so there's no shortcut here. – Kayaman Sep 13 '17 at 06:54

1 Answers1

1

You can find out the field names and types of a composite type with a query like this:

SELECT a.attname, a.atttypid::regtype
FROM pg_attribute a
   JOIN pg_class c ON a.attrelid = c.oid
WHERE c.relname = 'address_type'
  AND c.relkind = 'c'
  AND NOT a.attisdropped
  AND a.attnum > 0
ORDER BY a.attnum;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you very much for you answer. Also for others striving with meta data, i found [this](https://dba.stackexchange.com/questions/35497/display-user-defined-types-and-their-details) very useful in this regard. – alex Sep 13 '17 at 07:16