Gastos Abertos/Analise Planilha Execucao

Pelo levantamento preliminar realizado temos os seguintes campos comuns a todas as tabelas: cd_anoexecucao, cd_despesa, cd_exercicio, cd_orgao, cd_unidade. Um campo string livre (mantindo em NULL) pode ser adicionado para eventuais desambiguações para casos não previstos, cd_auxiliar.

A solução pode ser expressa em "dois sabores" diferentes: array de textos (complementadas pela array dos cabeçalhos) e JSON (ou ainda array-JSON para permitir tipagem hibrida numa array).

usando TEXT

Há que se armazenar o "header" da planilha (tabela de dados brutos).

CREATE TABLE gastos_head (  -- headers das planilhas de referência
  id serial PRIMARY KEY,
  reftab varchar(40) NOT NULL, -- nome de uma tabela-referência (planilha) de origem
  reftab_vers integer NOT NULL,  -- versao dos dados, id da tabela-referência (ex. ano)
  repotab varchar(40) NOT NULL DEFAULT 'gastos_pmsp', -- nome da tabela-destino neste repositório   
  info_head    text[], -- nome dos campos, variam de ano a ano (versao a versao) 
  UNIQUE(reftab,reftab_vers)
);

CREATE TABLE tmp_carga ( -- tabela (isolada) temp. para análise data scraping 
  id serial PRIMARY KEY,   -- opcional
  reftab_id integer NOT NULL REFERENCES gastos_head(id), -- opcional
  info   text[], -- todos os dados aqui
); -- o primeiro insert da planilha (CSV) pode ser feito nesta tabela. Testando chave-candidata: 
   -- SELECT DISTINCT info[1], infor[3], ..., info[N] FROM  tmp_carga 

CREATE TABLE gastos_pmsp ( -- tabela definitiva, otimizada e relacionada
  id serial PRIMARY KEY,  -- chave de uso interno (joins e permalinks)
  -- campos utilizados em joins, buscas e micro-services:
  reftab_id integer NOT NULL REFERENCES gastos_head(id),
  cd_anoexecucao integer NOT NULL, 
  cd_despesa integer NOT NULL, 
  cd_exercicio integer NOT NULL, 
  cd_orgao integer NOT NULL, 
  cd_unidade integer NOT NULL, 
  cd_auxiliar varchar(255),
  info   text[], -- demais campos, variam de ano para ano 
  UNIQUE (cd_anoexecucao, cd_despesa, cd_exercicio, cd_orgao, cd_unidade, cd_auxiliar)
);

-- --
-- função para caso de desejar popular gastos_pmsp sem redundância
CREATE FUNCTION array_splicebyindex(anyarray,integer[]) RETURNS anyarray AS
$BODY$
  WITH t AS (
     SELECT unnest($1) AS x, generate_subscripts($1, 1) AS i
  )  SELECT array_agg(x) FROM t WHERE not( i=ANY($2));
$BODY$ LANGUAGE 'sql' IMMUTABLE;
-- ex. SELECT  array_splicebyindex(array['a','b','c','d'], array[1,2] ) -- retorna c,d

usando JSON

CREATE TABLE gastos_pmsp (
  id serial PRIMARY KEY,  -- chave de uso interno (joins e permalinks)
  -- campos utilizados em joins, buscas e micro-services:
  cd_anoexecucao integer NOT NULL, 
  cd_despesa integer NOT NULL, 
  cd_exercicio integer NOT NULL, 
  cd_orgao integer NOT NULL, 
  cd_unidade integer NOT NULL, 
  cd_auxiliar varchar(255),
  info   JSON, -- demais campos, variam de ano para ano 
  UNIQUE (cd_anoexecucao, cd_despesa, cd_exercicio, cd_orgao, cd_unidade, cd_auxiliar)
);

CREATE VIEW gastos_pmsp_formated AS  -- apenas exemplo! 
  WITH t AS (
    SELECT *, DATE info->>'datainicial' AS datainicial , DATE info->>'datafinal' AS datafinal
    FROM   gastos_pmsp
  ) SELECT *, to_char(datainicial, 'DD/MM/YYYY') AS datainicial_fmt,
              to_char(datafinal, 'DD/MM/YYYY') AS datafinal_fmt
    FROM t;

Essa proposta ainda precisa ser confirmada depois de uma avaliação com carga completa dos dados. É importante preservar os tipos de dados no JSON (ex. string ou number) e também criar uma VIEW SQL para "helper" de formatação de dados, como exemplificado com datas.

---

NOTA: não confundir o levantamento de modelagem de dados, que pressupõe dados originais com certa coerência, com o levantamento dos dados em si, como deste exemplo.