Uzyskujesz typ danych dla każdej kolumny z tabeli PostGIS?

Potrzebuję uzyskać typy danych kolumn dla wszystkich kolumn w tabeli, w tym typy geometrii. Chciałem wiedzieć, czy istnieje funkcja lub SQL, który daje coś takiego:

column_name | data_type
------------+--------------
gid         | integer
descr       | character varying(32)
class       | character varying(10)
area        | double precision
geom        | geometry(Polygon,3763)

Od kilku odpowiedzi na Stack Exchange Network i gis.stackexchange wiem, że mogę uzyskać pewne informacje z następującym zapytaniem:

SELECT 
    g.column_name,
    g.data_type,
    g.character_maximum_length,
    g.udt_name,
    f.type,
    f.srid
FROM 
     information_schema.columns as g JOIN
     geometry_columns AS f 
         ON (g.table_schema = f.f_table_schema and g.table_name = f.f_table_name )
WHERE
    table_schema = 'my_schema_name' and
    table_name = 'my_table_name'

Wynik:

column_name | data_type         | character_maximum_length | udt_name | type    | srid
------------+-------------------+--------------------------+----------+---------+------
gid         | integer           |                          |          |         |
descr       | character varying | 32                       |          |         |
class       | character varying | 10                       |          |         |
area        | double precision  |                          |
geom        | USER-DEFINED      |                          | geometry | Polygon | 3763

Ale czy jest jakiś bardziej praktyczny sposób na uzyskanie informacji w wymaganym formacie? Czy też muszę wejść w „świat” CASE WHENstruktur i konkatenacji ciągów, aby zebrać wszystkie atrybuty kolumn w jednej kolumnie w tym formacie?

Obawiam się, że niespodziewany typ danych zaskoczy mnie potrzebą innego atrybutu z tabeli information_schema.columns. Tj. W poprzedniej tabeli nie użyłem żadnego numeric (15,2)typu danych, który musiałby użyć innych atrybutów (precyzja numeryczna i skala numeryczna), aby zostały przeanalizowane przez PRZYKŁAD.

Teoria tak, choć można ją uznać za bardzo złożoną.

• Każda tabela (wybierz * z pg_class) ma kolumny.
• Każda kolumna (wybierz * z pg_attribute) opcjonalnie ma numer „typmod”.
• Dla typów z typmod (wybierz * z pg_type) dostępna będzie funkcja „typmodout”.
• Uruchomienie funkcji typmod out na numerze typmod zwróci ciąg, który można połączyć z nazwą typu, aby utworzyć rodzaj czytelnego dla użytkownika podpisu, do którego przywykłeś (wybierz „numeryczny” || numerictypmodout (786441)) (wybierz geography_typmod_out (1107460))

Ale, hej, psql generuje ciągi, które chcesz, jeśli spojrzymy na to, co SQL generuje, być może odpowiedź tam jest.

Rzeczywiście, istnieje funkcja magiczna, która pobiera maszynopis i typmod i zwraca magiczny ciąg.

select a.attname, format_type(a.atttypid, a.atttypmod) from pg_attribute a where attname = 'geog';

Dołączając do pg_class, powinieneś być w stanie uzyskać te informacje na tabelę.

Można go uzyskać za pomocą prostego zapytania SQL.

SELECT * from information_schema.columns where table_name='mytablename'

Z pomocą Paula Ramseya zrobiłem to w ten sposób:

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;

AKTUALIZACJA

W międzyczasie utworzyłem funkcję z pytaniem o określony typ danych kolumny

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;

Wykorzystanie jest:

SELECT vsr_get_data_type('schema_name.table_name','column_name')

jeśli chcesz sprawdzić typ geometrii, możesz zaznaczyć „udt_name” w „INFORMACJE_SCHEMA.KOLUMNY” i użyć go !:

select column_name,udt_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name =g