Início / dba / Perguntas / 195717
Accepted
as.beaulieu
Asked: 2018-01-19 10:58:39 +0800 CST

colunas de consulta do catálogo do sistema postgres da tabela

  • 772

Estou construindo um teste de integração em um aplicativo que provisiona um banco de dados para um usuário. O usuário criado para ele não é um superusuário, e não tem acesso ao schema_information.tables pois quando tento o seguinte script:

SELECT table_name
FROM information_schema.tables
WHERE table_schema='{schema}'

Recebo um retorno de 0, como deveria, pois esse usuário não tem permissão.

Estou tentando consultar o banco de dados para verificar as tabelas e as colunas criadas. Posso obter a lista de nomes de tabelas através do catálogo do sistema com o seguinte script:

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = '{schema}'

E isso gera os nomes das tabelas do jeito que eu quero:

empresa, localização, pessoa, etc...

Não consigo encontrar o script com o catálogo do sistema para encontrar os nomes das colunas ( E como bônus, o tipo de dados ) de cada tabela. Até agora tentei o seguinte:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE  attrelid = 'business'

e aqui está o erro:

ERROR:  invalid input syntax for type oid: "business"
LINE 1: ...od) AS type FROM   pg_attribute WHERE  attrelid = 'business'
                                                         ^```

Também tentei:

SELECT
    a.attname as "Column",
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
    pg_catalog.pg_attribute a
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND a.attrelid = (
        SELECT c.oid
        FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ '**Dont know what to put here, Schema? Database?**'
            --AND pg_catalog.pg_table_is_visible(c.oid)
    );

e isso retorna 0 com um esquema ou banco de dados. Não tenho certeza do que colocar lá para c.relname. Também estou vendo 0 com isso porque um usuário básico simplesmente não pode ver mais profundamente do que tabelas em um esquema, ponto final?

postgresql information-schema

1 respostas

  1. Best Answer

    Basta selecionar em information_schema.columnsvez disso.

    SELECT table_catalog, table_schema, table_name, data_type
FROM information_schema.tables
WHERE table_schema='{schema}';

    PARA GLÓRIA

    Por qualquer motivo, se você não puder consultar information_schema.columns(o que, de outra forma, indicaria que algo é estranho para mim). Podemos fazer engenharia reversa dos catálogos de forma eficiente psql -Ecom um usuário que pode. \Em seguida, execute o comando client ( ) apropriado psqlque mostra o que você deseja, como \d schema. E copie as partes da consulta exportada que você precisa.

    Para mim, a coluna final é algo como

    SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '1024334' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

    Agora só preciso pegar todos os attrelid's para o esquema. Correndo rápido \d asdofkodskfeu vejo,

    SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(asdofkodskf)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

    n.nspnameNa verdade não queremosc.relname

    Então, dada a minha versão do psql, o método oficial de consultar o catálogo diretamente seria...

    SELECT c.oid,
  n.nspname,
  c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT a.attname,
    pg_catalog.format_type(a.atttypid, a.atttypmod),
    (
      SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
      FROM pg_catalog.pg_attrdef d
      WHERE d.adrelid = a.attrelid
        AND d.adnum = a.attnum
        AND a.atthasdef
    ),
    a.attnotnull, a.attnum,
    (
      SELECT c.collname
      FROM
        pg_catalog.pg_collation c,
        pg_catalog.pg_type t
      WHERE c.oid = a.attcollation
        AND t.oid = a.atttypid
        AND a.attcollation <> t.typcollation
    ) AS attcollation
  FROM pg_catalog.pg_attribute a
  WHERE a.attrelid = c.oid
    AND a.attnum > 0
    AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(public)$'  -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

    Trecho (sem todas as colunas ou todas as linhas) (relname é tabela, attname é coluna)

       oid   | nspname |           relname           |       attname        |       format_type       
llation 
---------+---------+-----------------------------+----------------------+-------------------------
--------
 1024242 | public  | spatial_ref_sys_pkey        | srid                 | integer                 
 1045853 | public  | product_discount_qty_excl   | qty                  | int4range               
 1024334 | public  | valid_detail                | valid                | boolean                 
 1024334 | public  | valid_detail                | reason               | character varying       
 1024334 | public  | valid_detail                | location             | geometry                
 1045847 | public  | product_discount            | pid                  | integer                 
 1045847 | public  | product_discount            | qty                  | int4range               
 1045847 | public  | product_discount            | percent_modifier     | real                    
 1024569 | public  | geography_columns           | f_table_catalog      | name
    • 4

relate perguntas