Getting data type of each column from PostGIS table?
The theory yes, though you could find it very complex indeed.
- Every table (select * from pg_class) has columns.
- Every column (select * from pg_attribute) optionally has a "typmod" number.
- For types with typmod (select * from pg_type) there will be a "typmodout" function.
- Running the typmod out function on a typmod number will return a string that can be concatenated with the type name to form the kind of user-readable signature you're used to (select 'numeric' || numerictypmodout(786441)) (select geography_typmod_out(1107460))
But, hey, psql generates the strings you want, if we look at what SQL it generates, maybe the answer is in there.
Sure enough, there is a magic function that takes a typeid and typmod and returns the magic string.
select a.attname, format_type(a.atttypid, a.atttypmod) from pg_attribute a where attname = 'geog';
With a join to pg_class you should be able to get this info per-table.
With Paul Ramsey help I made it this way:
SELECT a.attname as column_name, format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_attribute a
JOIN pg_class b ON (a.attrelid = b.relfilenode)
WHERE b.relname = 'my_table_name' and a.attstattarget = -1;
UPDATE
Meanwhile I have created a function to ask for a certain column data type
CREATE OR REPLACE FUNCTION "vsr_get_data_type"(_t regclass, _c text)
RETURNS text AS
$body$
DECLARE
_schema text;
_table text;
data_type text;
BEGIN
-- Prepare names to use in index and trigger names
IF _t::text LIKE '%.%' THEN
_schema := regexp_replace (split_part(_t::text, '.', 1),'"','','g');
_table := regexp_replace (split_part(_t::text, '.', 2),'"','','g');
ELSE
_schema := 'public';
_table := regexp_replace(_t::text,'"','','g');
END IF;
data_type :=
(
SELECT format_type(a.atttypid, a.atttypmod)
FROM pg_attribute a
JOIN pg_class b ON (a.attrelid = b.oid)
JOIN pg_namespace c ON (c.oid = b.relnamespace)
WHERE
b.relname = _table AND
c.nspname = _schema AND
a.attname = _c
);
RETURN data_type;
END
$body$ LANGUAGE plpgsql;
The usage is:
SELECT vsr_get_data_type('schema_name.table_name','column_name')
It can be get using a simple SQL query.
SELECT * from information_schema.columns where table_name='mytablename'