Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: System Tables of PostgreSQL

    Hi guys,

    I'm not a pro in DB, I got a work to do envolving the internal tables on PostgreSQL, and I'm not sure how to do it.

    I have the following tables


    Client
    Product
    Supplier
    Sales
    SalesItems
    Purchases

    It is a basic table, I need to create a data dictionary using these tables using System Tables of PostegreSQL


    I must return the following results

    Field | Type | identity | pk | size | null or not | fk

    Any help would be very apreciated.

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Is this for an academic assignment? That should be the only time you're mucking around with the system catalog unless you are a very advanced user. The catalog is managed thru user commands like create table, alter table and scripts like createdb and createlang. So create your tables using the CREATE TABLE command and you can look up their info in the catalog.

    This will get you pointed in the right direction.

    Code:
    SELECT *
    FROM information_schema.tables t
    JOIN information_schema.columns c ON t.table_name = c.table_name
    WHERE t.table_schema = 'public'
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: System Catalogs
    Lorenzo Alberton - Articles - Extracting META information from PostgreSQL (INFORMATION_SCHEMA)

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Also, refer to Chapter 33. The Information Schema in the PostgreSQL documentation.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Aug 2009
    Posts
    2

    Thumbs up

    Thank you guys

    I guess I got something here. And yes, it is a study work...

    Code:
    -- Obtém as tabelas do banco de dados
    -- Schema: "public"
    
    
    -- Se a sequencia e as tabelas existirem elas serão apagadas
    
    DROP TABLE IF EXISTS ItensDaVenda;
    DROP TABLE IF EXISTS ProdutosDoFornecedor;
    DROP TABLE IF EXISTS Vendas;
    DROP TABLE IF EXISTS Cliente;
    DROP TABLE IF EXISTS Compras;
    DROP TABLE IF EXISTS Fornecedor;
    DROP TABLE IF EXISTS Produto;
    DROP SEQUENCE IF EXISTS seq;
    
    CREATE SEQUENCE seq;
    
    CREATE TABLE Fornecedor
    (
    	codFornecedor INT DEFAULT nextval('seq') NOT NULL ,
    	nome VARCHAR(100) NOT NULL,
    	tel INT,
    	cnpj INT,
    	PRIMARY KEY(codFornecedor)
    );
    
    CREATE TABLE Produto
    (
    	codProduto INT DEFAULT nextval('seq') NOT NULL,
    	descricao VARCHAR(200),
    	qtdade INT NOT NULL,
    	precoRevenda INT,
    	precoDistrubuicao INT,
    
    	PRIMARY KEY(codProduto)
    );
    
    CREATE TABLE Cliente 
    (
    	idCliente INT DEFAULT nextval('seq') NOT NULL,
    	nome VARCHAR(100),
    
    	PRIMARY KEY (idCliente)
    );
    
    CREATE TABLE Compras
    (
    	numNF INT DEFAULT nextval('seq') NOT NULL,
    	codFornecedor INT NOT NULL,
    	codProduto INT NOT NULL,
    	data DATE,
    
    	PRIMARY KEY(numNF),
    	FOREIGN KEY(codFornecedor) REFERENCES Fornecedor,
    	FOREIGN KEY(codProduto) REFERENCES Produto
    );
    
    CREATE TABLE Vendas
    (
    	numNF INT DEFAULT nextval('seq') NOT NULL,
    	idCliente INT NOT NULL,
    	valorTotal INT NOT NULL,
    
    	PRIMARY KEY (numNF),
    	FOREIGN KEY (idCliente) REFERENCES Cliente
    );
    
    CREATE TABLE ItensDaVenda
    (
    	codProduto INT DEFAULT nextval('seq') NOT NULL,
    	numNF INT DEFAULT nextval('seq') NOT NULL,
    
    	PRIMARY KEY(codProduto),
    	FOREIGN KEY(codProduto) REFERENCES Produto,
    	FOREIGN KEY(numNF) REFERENCES Vendas
    );
    
    CREATE TABLE ProdutosDoFornecedor
    (
    	codFornecedor INT DEFAULT nextval('seq') NOT NULL,
    	codProduto INT NOT NULL,
    	quantidade INT NOT NULL,
    
    	PRIMARY KEY(codFornecedor),
    	FOREIGN KEY(codProduto) REFERENCES Produto
    );
    
    
    
    -- Seleciona as tabelas e mostra quem são as primary e secundary keys
    SELECT c.relname AS tabela,
           a.attname AS coluna,
           t.typname AS tipo,
           a.attnotnull AS nulo,	
           co.conname AS constraint_name           
    	FROM pg_class c 
    	INNER JOIN pg_attribute a ON (a.attrelid = c.oid)
    	INNER JOIN pg_type t ON (a.atttypid = t.oid)
    	LEFT JOIN pg_index i ON (i.indrelid = c.oid)
    	LEFT JOIN pg_constraint co ON (co.conrelid  = c.oid)
    	WHERE c.relkind = 'r'
    	AND relname !~ '^(pg_|sql_)'
    	AND a.attnum > 0
    	AND a.attrelid = c.oid
    	AND a.atttypid = t.oid
    	AND (indisunique = 't' OR indisprimary = 't')
    	ORDER BY a.attnum;
    With this code is possible create the database and select its structure with some fields filtered. I got a very good help here also

    http://www.alberton.info/postgresql_meta_info.html

    It is a complete article explaining how to do that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •