Zapytanie, aby zwrócić wyjściowe nazwy kolumn i typy danych zapytania, tabeli lub widoku


21

Czy istnieje zapytanie lub polecenie PostgreSQL, które zwraca nazwy pól i typy pól zapytania, tabeli lub widoku?

Np. Rozwiązanie zastosowane do prostej kwerendy SELECT, takie jak, SELECT * from personpowinno zwrócić listę taką jak:

Column Name   | Column Type
===========================
First Name    | character
Last Name     | character
Age           | integer
Date of Birth | date

Przejrzałem information_schemapoglądy opisane w odpowiedzi poniżej i wydaje się, że całkiem dobrze pokrywają tabele, i podejrzewam, że obejmuje to również poglądy, ale jeszcze tego nie sprawdziłem.

Ostatnim jest jakaś arbitralna, ale ważne np kwerendy SELECT udziałem, JOINS, UNIONSetc, w bazie danych. Czy istnieje wbudowana procedura lub inna procedura składowana lub skrypt, który może zwrócić to samo dla dowolnego ważnego zapytania?

Tworzę program, który tworzy dane i formularze zapytań, a informacje są potrzebne do sprawdzania poprawności danych i wykonywania funkcji na zwróconych danych.


Oczywiście nie ma jednego „polecenia”, ale istnieją różne sposoby wyszukiwania informacji z katalogów systemowych. Zadaj konkretne pytanie , dodaj przykład i czego oczekujesz w zamian, i daj nam wyobrażenie o zamiarze, który za tym stoi.
Erwin Brandstetter

1
Powodem tej prostoty jest to, że w odniesieniu do klientów zapytania, tj. SELECTZapytania, tj. Zapytania nie definiujące danych lub manipulujące danymi, czy to w tabelach, widokach lub innych zapytaniach, zwracają wiersze i kolumny danych, więc PostgreSQL powinien mieć możliwość zwrócenia lista nazw kolumn i ich typów danych. Te information_schemapoglądy wymienione poniżej w odpowiedziach pojawia się odpowiedzieć na tabele i widoki. Arbitralne zapytania SELECT są ostatnią granicą.
Przeredaguję

Odpowiedzi:


22

Schemat informacyjny a katalogi systemowe

Rozmawialiśmy o tym wiele razy. Schemat informacyjny służy określonym celom. Jeśli znasz się na katalogach systemowych, lepiej służą one większości celów , IMO. Katalogi systemowe są faktycznym źródłem wszystkich informacji.

Schematu informacji dostarcza znormalizowane widoki, które pomagają z przenośności, głównie po drugiej wersji główne PostgreSQL, ponieważ przenośność między różnymi platformami RDBMS zazwyczaj jest iluzją, gdy Twoje zapytania są wyrafinowane wystarczy potrzeby patrzenia w górę katalogów systemowych. Co ważne, Oracle nadal nie obsługuje schematu informacji.

Widoki w schemacie informacyjnym muszą przeskakiwać przez wiele obręczy, aby osiągnąć format zgodny ze standardem. Powoduje to, że są powolne, a czasem bardzo wolne. Porównaj plany i wydajność dla tych podstawowych obiektów:

EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

Różnica jest niezwykła. To naprawdę zależy od tego, czego szukasz.

Twój przykład

Na przykład SELECT * from tblporównaj dwa poniższe zapytania do tej prostej tabeli:

CREATE TEMP TABLE foo(
   A numeric(12,3)
 , b timestamp(0)
);

Używanie pg_attribute:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

format_type() zwraca pełny typ ze wszystkimi modyfikatorami:

attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone

Zauważ też, że rzutowanie regclassrozwiązuje nazwę tabeli nieco inteligentnie zgodnie z bieżącym search_path. Zgłasza także wyjątek, jeśli nazwa jest niepoprawna. Detale:

Używanie information_schema.columns:

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'foo'
ORDER  BY ordinal_position;

Informacje są znormalizowane, ale niekompletne :

column_name | data_type
------------+----------------------------
a           | numeric
b           | timestamp without time zone

Aby uzyskać pełne informacje o typie danych, należy dodatkowo rozważyć wszystkie te kolumny:

character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision

Powiązane odpowiedzi:

Lista zalet i wad , największe zalety (IMO) pogrubione:

Widoki schematu informacyjnego

  • często prostsze (zależy)
  • powolny
  • wstępnie przetworzone, które mogą, ale nie muszą odpowiadać Twoim potrzebom
  • selektywne (użytkownicy widzą tylko obiekty, do których mają uprawnienia)
  • zgodny ze standardem SQL (który jest implementowany przez niektóre z głównych RDBMS)
  • głównie przenośne w głównych wersjach Postgres
  • nie wymagają dużej wiedzy na temat Postgres
  • identyfikatory są opisowe, długie i czasem niezręczne

Katalogi systemowe

  • często bardziej złożone (zależy), bliżej źródła
  • szybki
  • pełna (kolumny systemowe jakoid zestawie)
  • niezgodne ze standardem SQL
  • mniej przenośny w głównych wersjach Postgres (ale podstawy się nie zmienią)
  • wymagają bardziej szczegółowej wiedzy na temat Postgres
  • identyfikatory są zwięzłe, mniej opisowe, ale wygodnie krótkie

Dowolne zapytanie

Aby uzyskać tę samą listę nazw i typów kolumn z zapytania, możesz użyć prostej sztuczki: UTWÓRZ tabelę tymczasową z wyniku zapytania, a następnie użyj tych samych technik, jak powyżej.

Możesz dołączyć LIMIT 0, ponieważ nie potrzebujesz rzeczywistych danych:

CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT  0;

Aby uzyskać typ danych poszczególnych kolumn, możesz również użyć funkcji pg_typeof():

SELECT pg_typeof(1);

Dziękuję bardzo. Przez jakiś czas zastanawiałem się, jak uzyskać typ danych kolumny w pg_attribute i właśnie natknąłem się na ten post. Doceń swój post.
Melinda

Jest to ogólnie pomocne, ale nie odpowiada na pierwotne pytanie, w jaki sposób uzyskać informacje o typach danych kolumn dostarczanych przez instrukcję SELECT. To było nie o kolumnach tabel lub widoków, które oczywiście znajdować się w katalogach systemowych i są pokazane na schemacie informacyjnym, jak również.
Holger Jakobs


2

Jeśli masz dostęp do pg_catalog i używasz PgAdmin3, gorąco polecam rozwiązanie, które znalazłem na blogu Valentine's Tech ( http://tech.valgog.com/2011/02/pgadmin-iii-macros-get-table-fields. HTML ). Jest to makro PgAdmin3, do którego można uzyskać dostęp za pomocą skrótu, aby wyświetlić definicję nazwy wybranej tabeli.

select quote_ident(nspname) || '.' || quote_ident(relname) as table_name, 
       quote_ident(attname) as field_name, 
       format_type(atttypid,atttypmod) as field_type, 
       case when attnotnull then ' NOT NULL' else '' end as null_constraint,
       case when atthasdef then 'DEFAULT ' || 
                                ( select pg_get_expr(adbin, attrelid) 
                                    from pg_attrdef 
                                   where adrelid = attrelid and adnum = attnum )::text else ''
       end as dafault_value,
       case when nullif(confrelid, 0) is not null
            then confrelid::regclass::text || '( ' || 
                 array_to_string( ARRAY( select quote_ident( fa.attname ) 
                                           from pg_attribute as fa 
                                          where fa.attnum = ANY ( confkey ) 
                                            and fa.attrelid = confrelid
                                          order by fa.attnum 
                                        ), ','
                                 ) || ' )'
            else '' end as references_to
  from pg_attribute 
       left outer join pg_constraint on conrelid = attrelid 
                                    and attnum = conkey[1] 
                                    and array_upper( conkey, 1 ) = 1,
       pg_class, 
       pg_namespace
 where pg_class.oid = attrelid
   and pg_namespace.oid = relnamespace
   and pg_class.oid = btrim( '$SELECTION$' )::regclass::oid
   and attnum > 0
   and not attisdropped
 order by attrelid, attnum;

Działa jak urok i jest niezwykle przydatny.


1

Użyj tych information_schemapoglądów , są standardem SQL i zawierają informacje, które chcesz.

Można także bezpośrednio dostępu pg_class, pg_attributeitp, ale to nieprzenośne i często fiddlier; może trzeba pomocnicze funkcje, takie jakoidvectortypes , pg_get_function_argumentsitp dla niektórych rzeczy.

Jeśli chcesz zobaczyć, jak psqlwykonuje się coś takiego \dt, uruchom psql -E- wypisze zapytanie. Jednak zwykle lepiej jest użyć opcji, information_schemajeśli spełni ona Twoje potrzeby.


1

Może to być zbyt proste, ale pgAdmin4 pokazuje typy pól w wynikach wyjściowych. Inne powyższe rozwiązania są prawdopodobnie bardziej eleganckie, ale kiedy potrzebuję tylko szybkiej odpowiedzi, okazuje się, że GUI zapytania pgAdmin4 działa całkiem dobrze. Próba ustalenia typu pola pola obliczonego zwróconego przez widok lub funkcję może być trudna.

wprowadź opis zdjęcia tutaj


To bardzo miłe, że pgAdmin4 to robi, ale jak to zrobić? Czy możemy się dowiedzieć bez skanowania całego kodu źródłowego PgAdmin4?
Holger Jakobs
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.