PostgreSQL

Conteúdos relacionados ao banco PostgreSQL (Configuração, SQL's de apoio, etc.)

BACKUP - Resolução de problema com o pg_dump
Coalesce
Converter Binary Data (Bytea) para Text
Criação de View
Descobrir a maior versão de um procedimento
Descobrir SQL Executado
Erros de SQL comuns no banco PostgreSQL
Exportando consulta do banco de dados (Excel, TXT)
Exportando tabela em CSV (Excel)
Função - Formata CNPJ
Função - Formata CPF
Função - Primeiro e ultimo dia do mês
Função - Retorna parte de um campo de texto definindo inicio e fim
Função de agregação (count, sum, max, min, avg, partition by)
Função de Strings
Funções de data
Gerar log dos comandos
Operações Join
Order by, Group by e Having
Pacote de Funções DOX (Schema) - PostgreSQL
Resultados do SELECT separados por virgula
Retorna lista
Tamanhos da tabelas (MB)
Union, Union All, Except, Except All e Intersect
Utilização de Datas e Conversões (current_date, timestamp, to_char, etc)
With, Substr, Split_part e Strpos

BACKUP - Resolução de problema com o pg_dump

Ao tentar importar uma base e o diretório retornar erro no pg_dump. A resolução seguirá abaixo:

Em Barra inicial> Pesquisar por "Configurações avançadas do sistema"> Aba "avançado"> Clicar em "variáveis de ambiente" > editar path> novo> setar caminho do pg_dump (está dentro do disco local> arquivo de programa> seleciona arquivo do postgresql> seleciona pg_dump.hba). Após isso é só finalizar a operação com "ok".

Conforme seguirá em imagens:

Coalesce

Com a função COALESCE, é possível definir que, quando o campo retornar nulo, outro valor será retornado no lugar.

TABELA EXEMPLO

EX_PRODUTOS

| IDPRODUTO |    DESCRICAO    | VALOR_CUSTO | VALOR_VENDA |
-----------------------------------------------------------
|         1 | 'Arroz 1KG'     |        0.75 |      (null) |
|         2 | 'Feijão 1KG'    |        1.07 |        2.99 |
|         3 | 'Macarrão 500G' |      (null) |      (null) |

SQL de CRIAÇÃO/INSERÇÃO:

CREATE TABLE ex_produtos (
  idproduto INT4,
  descricao TEXT,
  valor_custo DECIMAL(15,2),
  valor_venda DECIMAL(15,2));
INSERT INTO ex_produtos VALUES (1, 'Arroz 1KG', 0.75, NULL),
                               (2, 'Feijão 1KG', 1.07, 2.99),
                               (3, 'Macarrão 500G', NULL, NULL);

COALESCE

A função COALESCE retorna o valor do parâmetro seguinte quando o anterior ser nulo. Pode ser usado quantos parâmetros de entrada for necessário.

SELECT	DESCRICAO,
	    COALESCE(VALOR_VENDA, VALOR_CUSTO, 0) AS VALOR
FROM EX_PRODUTOS

Retorno:

| DESCRICAO       | VALOR |
---------------------------
| 'Arroz 1KG'     |  0.75 |
| 'Feijão 1KG'    |  2.99 |
| 'Macarrão 500G' |     0 |

Converter Binary Data (Bytea) para Text

select convert_from(CAMPO_BINARY, 'UTF-8') from TABELA

Criação de View

A view é útil para ter uma consulta pré-organizada que é executada frequentemente.

Exemplo

Neste exemplo, temos uma tabela chamada CIDADE com informações de várias cidades de alguns estados, incluindo um campo que informa se o registro está inativo.

| IDCIDADE | DESCRICAO        | UF | INATIVO |
----------------------------------------------
|        0 | Desconhecido     | SC | S       |
|        1 | Criciúma         | SC | N       |
|        2 | São Paulo        | SP | N       |
|        3 | Balneário Rincão | SC | N       |
|        4 | Porto Alegre     | RS | N       |
|        5 | Araranguá        | SC | N       |
|        6 | Içara            | SC | N       |
|        7 | Curitiba         | PR | N       |
|        8 | Torres           | RS | N       |
|        9 | Florianópolis    | SC | N       |

Precisamos que na consulta, seja mostrado apenas cidades que não estão inativas com o código e a descrição concatenada com a UF (Ex: Florianópolis - SC). Além disso, só mostrar as cidades do estado SC e ordenado pela descrição.

CREATE OR REPLACE VIEW VW_CIDADE_SC AS
SELECT IDCIDADE AS CODIGO,
       DESCRICAO||' - '||UF AS DESCRICAO
  FROM CIDADE
 WHERE UF = 'SC'
   AND INATIVO = 'N'
 ORDER BY DESCRICAO

Chamada da view:

SELECT * FROM VW_CIDADE_SC

Retorno:

| CODIGO | DESCRICAO             |
----------------------------------
|      5 | Araranguá - SC        |
|      3 | Balneário Rincão - SC |
|      1 | Criciúma - SC         |
|      9 | Florianópolis - SC    |
|      6 | Içara - SC            |

Descobrir a maior versão de um procedimento

Olá, compartilhamos aqui com vocês um SQL que traz a informação de procedimento e sua versão mais recente, levando em conta se o processo está ativo. Caso necessário, podes personalizar o SQL para trazer outras informações:

SELECT IDPROCEDIMENTO, 
       DESCRICAO, 
       IDVERSAO 
  FROM CRM_PROCEDIMENTO X 
 WHERE IDVERSAO = (SELECT MAX (IDVERSAO) 
                     FROM CRM_PROCEDIMENTO 
                    WHERE IDPROCEDIMENTO = X.IDPROCEDIMENTO)
   AND INATIVO = 'N'   
 ORDER BY IDPROCEDIMENTO

Descobrir SQL Executado

Abaixo comando util para monitorar / descobrir SQLs executados, muito util para descobrir SQLs para relatórios, realizar debug de SQL montados durante um processo ou para demais necessidades:

SELECT USENAME,
       APPLICATION_NAME,
       QUERY_START,
       QUERY
FROM PG_STAT_ACTIVITY;

Foi montado uma visualização de exemplo, trazendo o usuário (Sim, o nome da coluna é USENAME, não esta errado), a aplicação que esta usando, a data hora que rodou e o SQL, caso queira montar uma visualização personalizada, executar o comando abaixo e verificar quais colunas são mais uteis para sua necessidade.

SELECT *
  FROM PG_STAT_ACTIVITY;

Consultando Query ativas / lentas que estão afetando o desempenho do banco de dados.

SELECT *
  FROM PG_STAT_ACTIVITY
 WHERE UPPER(DATNAME) = UPPER('ema')
   AND UPPER(STATE)   = UPPER('active');

Erros de SQL comuns no banco PostgreSQL

Invalid input syntax for integer

Esse erro ocorre quando se tenta dar um CAST em um campo inteiro com um valor inválido.

SELECT CAST('123x' AS INTEGER)
  FROM VERSAODB

SOLUÇÃO abaixo:

SELECT CAST('123' AS INTEGER)
  FROM VERSAODB

UNION types character varying and integer cannot be matched

Isso ocorrerá quando se está utilizando um comando UNION e os tipos de campos das colunas selecionadas são diferentes.

SELECT P.DOCUMENTO
  FROM PAGAR P 

 UNION ALL 

SELECT CP.IDDOC
  FROM CHEQUE_PRE CP

SOLUÇÃO abaixo: A solução é sempre igualar os tipos.

SELECT P.DOCUMENTO
  FROM PAGAR P

 UNION ALL

SELECT CAST(CP.IDDOC AS VARCHAR)
  FROM CHEQUE_PRE CP

current transaction is aborted, commands ignored until end of transaction block

Isso começa a ocorrer quando você está em uma sessão com o AUTO COMMIT desabilitado onde você executa um SQL Statement (SELECT * FROM...) e o mesmo tem um na sua sintaxe.

Quando isso ocorre o PostgreSQL aborta e não termina a execução do comando e sendo necessário fazer um ROLLBACK. Quando o AUTO COMIT está habilitado o ROLLBACK é automático. Porém os COMMIT também.

date/time field value out of range: "05/31/2019 00:00:00"

Este erro acontece quando é tentado filtrar uma data/hora em um campo que o tipo dele é somente data.

SELECT * 
  FROM NFS
 WHERE NFS.DATAEMISSAO BETWEEN '05/31/2019 00:00:00' AND '05/31/2021 00:00:00'

SOLUÇÃO abaixo:

SELECT * 
  FROM NFS
 WHERE NFS.DATAEMISSAO BETWEEN '2019-05-31' AND '2019-05-31'

Exportando consulta do banco de dados (Excel, TXT)

Neste fórum vamos mostrar como exportar sua consulta SQL para um arquivo CSV ou um TXT gerado em sua máquina. Ex: Utilize o seguinte comando para criar o arquivo:

COPY  
(  SELECT C.IDCLIFOREMP AS IDCLIFOREMP, 
             C.FANTASIA AS NOME,
             U.USERNAME AS USERNAME,
                C.EMAIL AS EMAIL,
            D.DESCRICAO AS DEPARTAMENTO,
            STRING_AGG(BF.DESCRICAO,', ')
     FROM CLIFOREMP C
     JOIN USUARIO U ON (C.IDCLIFOREMP = U.IDCLIFOREMP) 
	 JOIN USUARIO_PAPELFUNCAO UP ON (U.IDUSUARIO = UP.IDUSUARIO)
	 JOIN BPM_FUNCAO BF ON (BF.IDBPMFUNCAO = UP.IDPAPELFUNCAO)
	 JOIN DEPARTAMENTOS D ON (C.IDDEPARTAMENTO = D.IDDEPARTAMENTO)
	WHERE U.INATIVO = 'N' 
	  AND C.INATIVO = 'N'
	GROUP BY C.IDCLIFOREMP, 
	         C.FANTASIA, 
	         U.USERNAME, 
	         C.EMAIL, 
	         D.DESCRICAO)

TO 'C:/temp/teste.csv' 
DELIMITER ';'  
CSV HEADER

COPY — Comando que é utilizado para a exportação do arquivo.
( ) — Dentro do parênteses, abaixo do comando, é onde estará o SQL que irá trazer os dados para a geração do arquivo. Como exemplo, fiz um SQL que traga todos os papeis funções de todos os usuários do DOX.
TO 'C:/temp/teste.csv' — Local onde o arquivo será gerado e a extensão. Caso queira trocar para um TXT por exemplo, apenas mudar o nome da extensão.
DELIMITER ';' — Após o “Delimiter” deverá ser colocado o separador desejado. O separador pode ser ";", ",", "|", etc… neste caso, foi utilizado o “;” como exemplo.
CSV HEADER — Utiliza-se esse comando caso queira que o nome das colunas apareçam no arquivo gerado(cabeçalho).

Para que o arquivo seja criado, sempre verificar as permissões das pastas do diretório.

Esse comando de exportar a consulta pode ser usado em um processo, por exemplo, utilizando o evento Registro banco dados - SQL - Executar, com a opção “comando” marcado no conector.

Exportando tabela em CSV (Excel)

Estamos compartilhando com todos uma maneira pratica de exportar uma tabela inteira do banco de dados PostgreSQL para o formato CSV para ser manipulado fora do banco, exemplo, no Microsoft Excel como planilha:

COPY CRM_PROCESSO_XML
  TO 'C:/EMATEMP/TABELA.csv' CSV;

No exemplo acima, exportei todas as NFe importadas no DOX para um arquivo CSV.

IMPORTANTE:

O arquivo é gerado no servidor onde esta rodando o serviço do postgreSQL.
O diretorio precisa existir.
O diretorio precisa ter permissão para leitura e gravação.

Função - Formata CNPJ

SQL de criação:

create or replace function formata_cnpj (xcnpj in varchar) returns varchar(18) as
$body$
declare
  retorno varchar(18);
begin

  select substr(lpad(xcnpj, 14, '0'),1,2) || '.' ||
         substr(lpad(xcnpj, 14, '0'),3,3) || '.' ||
         substr(lpad(xcnpj, 14, '0'),6,3) || '/' ||
         substr(lpad(xcnpj, 14, '0'),9,4) || '-' ||
         substr(lpad(xcnpj, 14, '0'),13,2) into retorno;

  return retorno;
end;
$body$
language plpgsql;

Chamada da função:

select formata_cnpj('12345678000910')

Retorno:

12.345.678/0009-10

Função - Formata CPF

SQL de criação:

create or replace function formata_cpf (xcpf in varchar) returns varchar(11) as
$body$
declare
  retorno varchar(14);
begin
  select substr(lpad(xcpf, 11, '0'),1,3) || '.' ||
         substr(lpad(xcpf, 11, '0'),4,3) || '.' ||
         substr(lpad(xcpf, 11, '0'),7,3) || '-' ||
         substr(lpad(xcpf, 11, '0'),10,2) into retorno;
    
  return retorno;
end;
$body$
language plpgsql;

Chamada da função:

select formata_cpf('12345678910')

Retorno:

123.456.789-10

Função - Primeiro e ultimo dia do mês

Olá, neste fórum iremos apresentar duas funções do PostgreSQL que podem ser utilizadas para retornar o primeiro e último dia do mês corrente.

Primeiro dia do mês

SELECT cast(date_trunc('month',current_date) as date);

Último dia do mês

SELECT cast(date_trunc('month', current_date) + INTERVAL'1 month' - INTERVAL'1 day' as date);

Nesta última query foi utilizado o comando INTERVAL, para saber mais sobre esta função, acesse a documentação oficial do PostgreSQL clicando aqui.

Retorno da query executada:

Função - Retorna parte de um campo de texto definindo inicio e fim

SQL de criação da função:

create or replace function retorna_parte (xtexto in text, xdesde in text, xate in text, xcont in int4) returns text as $body$
declare
    info text;
begin
	select substr(split_part(xtexto, xdesde, xcont+1), 1, strpos(split_part(xtexto, xdesde, xcont+1), xate)-1)
      into info;
    return info;
end;
$body$
language plpgsql;

Chamada da função:

select retorna_parte('<nome>João da Silva</nome><dtnascimento>30/03/1990</dtnascimento><profissao>Consultor DOX</profissao>', '<nome>', '</', 1) as nome,
       retorna_parte('<nome>João da Silva</nome><dtnascimento>30/03/1990</dtnascimento><profissao>Consultor DOX</profissao>', '<dtnascimento>', '</', 1) as dtnascimento,
       retorna_parte('<nome>João da Silva</nome><dtnascimento>30/03/1990</dtnascimento><profissao>Consultor DOX</profissao>', '<profissao>', '</', 1) as profissao

Retorno:

|     NOME      |    DATA    |   PROFISSAO   |
----------------------------------------------
| João da Silva | 30/03/1990 | Consultor DOX |

Função de agregação (count, sum, max, min, avg, partition by)

Confira algumas funções que ajudam nas consultas, onde é necessário criar agrupamentos de diferentes formas.

TABELA EXEMPLO

EX_ITENS

| CODIGO | DESCRICAO      | TIPO     | QUANTIDADE |
---------------------------------------------------
|      1 | 'Biscoito'	  | 'Comida' |         20 |
|      2 | 'Água'         | 'Bebida' |      47,95 |
|      3 | 'Suco'         | 'Bebida' |       23,4 |
|      4 | 'Pão'          | 'Comida' |         10 |
|      5 | 'Refrigerante' | 'Bebida' |      13,75 |

SQL de CRIAÇÃO/INSERÇÃO:

CREATE TABLE ex_itens (
  codigo INT4,
  descricao TEXT,
  tipo VARCHAR(20),
  quantidade DECIMAL(15,2));
INSERT INTO ex_itens VALUES (1, 'Biscoito', 'Comida', 20),
                            (2, 'Água', 'Bebida', 47.95),
                            (3, 'Suco', 'Bebida', 23.4),
                            (4, 'Pão', 'Comida', 10),
                            (5, 'Refrigerante', 'Bebida', 13.75);
COMMIT;

Consulta com agrupamento agregado:

SELECT SUM(QUANTIDADE) VOLUME_POR_TIPO,
	   MIN(QUANTIDADE) MENOR_QTD_POR_TIPO,
	   MAX(QUANTIDADE) MAIOR_QTD_POR_TIPO,
	   TRUNC(AVG(QUANTIDADE), 2) MEDIA_QTD_POR_TIPO
FROM EX_ITENS
GROUP BY TIPO

Retorno:

| QTD_POR_TIPO | VOLUME_POR_TIPO | MENOR_QTD_POR_TIPO | MAIOR_QTD_POR_TIPO | MEDIA_QTD_POR_TIPO |
-------------------------------------------------------------------------------------------------
|            3 |           85.10 |              13.75 |              47.95 |              28.36 |
|            2 |              30 |                 10 |                 20 |                 15 |

Count: Retorna quantidade de linhas no agrupamento definido;
Sum: Retorna soma de uma coluna das linhas no agrupamento definido;
Max: Retorna valor máximo do campo de entrada no agrupamento definido;
Min: Retorna valor mínimo do campo de entrada no agrupamento definido;
Avg: Retorna média dos valores do campo de entrada no agrupamento definido;

Consulta com agrupamento analítico:

SELECT CODIGO, DESCRICAO, TIPO, QUANTIDADE,
       DENSE_RANK() OVER (ORDER BY QUANTIDADE DESC) RANK_QTD,
       DENSE_RANK() OVER (PARTITION BY TIPO ORDER BY QUANTIDADE DESC) RANK_QTD_POR_TIPO,
       COUNT(QUANTIDADE) OVER (PARTITION BY TIPO) QTD_ITENS_POR_TIPO,
       SUM(QUANTIDADE) OVER (PARTITION BY TIPO) QTD_VOLUME_POR_TIPO,
       MIN(QUANTIDADE) OVER (PARTITION BY TIPO) MENOR_QTD_POR_TIPO,
       MAX(QUANTIDADE) OVER (PARTITION BY TIPO) MAIOR_QTD_POR_TIPO,
       TRUNC(AVG(QUANTIDADE) OVER (PARTITION BY TIPO), 2) MEDIA_QTD_POR_TIPO
  FROM EX_ITENS

Retorno:

| CODIGO | DESCRICAO      | TIPO     | QUANTIDADE | RANK_QTD | RANK_QTD_POR_TIPO | QTD_ITENS_POR_TIPO | QTD_VOLUME_POR_TIPO | MENOR_QTD_POR_TIPO | MAIOR_QTD_POR_TIPO | MEDIA_QTD_POR_TIPO |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|      2 | 'Água'         | 'Bebida' |      47.95 |        1 |                 1 |                  3 |                85.1 |              13.75 |              47.95 |              28.36 |
|      3 | 'Suco'         | 'Bebida' |       23.4 |        2 |                 2 |                  3 |                85.1 |              13.75 |              47.95 |              28.36 |
|      1 | 'Biscoito'     | 'Comida' |         20 |        3 |                 1 |                  2 |                  30 |                 10 |                 20 |                 15 |
|      5 | 'Refrigerante' | 'Bebida' |      13.75 |        4 |                 3 |                  3 |                85.1 |              13.75 |              47.95 |              28.36 |
|      4 | 'Pão'          | 'Comida' |         10 |        5 |                 2 |                  2 |                  30 |                 10 |                 20 |                 15 |

Dense_rank(): Retorna posição de rank definida pela ordenação na estrutura com Over;
Partition by: Utilizado para retornar um valor agrupado sem fazer um Distinct.

Função de Strings

Concatenação de strings - dois || (pipes)

SELECT 'ae' || 'io' || 'u' --> Retorno = 'aeiou'

SELECT CHR(67)||CHR(65)||CHR(84) --> Retorno = 'CAT'

Quantidade de caracteres de string

SELECT CHAR_LENGTH('UNIFOR') --> Retorno = 6

SELECT LENGTH('Database') --> Retorno = 8

Converter para minúsculas

SELECT LOWER('UNIFOR') --> Retorno = 'unifor'

Converter para maiúsculas

SELECT UPPER('universidade') --> Retorno = 'UNIVERSIDADE'

Posição de caractere

SELECT POSITION ('@' IN 'bando@emasoftware.com.br') --> Retorno = 6

SELECT STRPOS('Ema Software' ,'Soft') --> Retorno = 5

Substring

SELECT SUBSTRING ('Ema DOX' FROM 5 FOR 3) --> Retorno = 'DOX'

SELECT SUBSTRING ('PostgreSQL' FROM '.......') --> Retorno = 'Postgre'

SELECT SUBSTRING ('PostgreSQL' FROM '...$') --> Retorno = 'SQL' (últimos caracteres da String)

SELECT SUBSTR ('Ema Software', 5, 8) --> Retorno = 'Software'

Substituir todos os caracteres semelhantes

SELECT TRANSLATE('Brasil', 'il', 'ão') --> Retorno = 'Brasão'

SELECT TRANSLATE('Brasileiro', 'eiro', 'eira') --> Retorno = 'Brasileira'

Remover espaços de strings

SELECT TRIM(' SQL - PADRÃO ') --> Retorno = 'SQL - PADRÃO' (remove espaços de ambos os lados da string)

SELECT RTRIM(' SQL - PADRÃO ') --> Retorno = ' SQL - PADRÃO'  (remove espaços do lado direito da string)

SELECT LTRIM(' SQL - PADRÃO ') --> Retorno = 'SQL - PADRÃO '  (remove espaços do lado esquerdo da string)

Calcular MD5 de String

SELECT MD5('Ema Software') --> Retorno = '50579faffec425086c8dc0f9fdbfd3be'

Repetir uma string N vezes

SELECT REPEAT('SQL-', 3); - - Retorna SQL-SQL-SQL-

Sobrescrever substring em string

SELECT REPLACE ('Postgresql', 'sql', 'SQL') --> Retorno = 'PostgreSQL'

Dividir cadeia de caracteres com delimitador

SELECT SPLIT_PART( 'Guarda-chuva', '-', 1) --> Retorno = 'Guarda'

SELECT SPLIT_PART( 'Guarda-chuva', '-', 2) --> Retorno = 'chuva'

Iniciais maiúsculas

SELECT INITCAP('nome sobrenome') --> Retorno = 'Nome Sobrenome'

Funções de data

1 - Funções básicas:

CURRENT_DATE - Data Atual
CURRENT_TIME - Hora Atual 
CURRENT_TIMESTAMP - Data e Hora Atual

2 - Funções DATE_TRUNC:

DATE_TRUNC - Especificar parte ser truncada em data informada

Com essa função é possivel obter o parametro informado, ignorando os detamais dados referentes a data, sua utilização se dá date_trunc('CAMPO', ORIGEM), onde CAMPO é o tipo de dado e ORIGEM a data a qual deve ser truncada, abaixo lista de todos os CAMPOS disponiveis:

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

3 - Exemplos de uso:

SELECT date_trunc('hour', TIMESTAMP '2020-01-16 20:38:40');
Result: 2020-01-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2020-02-16 20:38:40');
Result: 2020-01-01 00:00:00


Truncar para obter 1º dia do mês 
date_trunc('month',current_date)

Gerar log dos comandos

Neste tópico veremos como configurar o PostgreSQL para gerar um log de todos os comando executados.

** Atenção : Para realizar este procedimento os serviços do postgreSQL devem estar parados, para isso basta acessar o gerenciador de tarefas e parar os serviços ou pressionar windows + R e digitar services.msc e procurar os serviços do postgres e parar eles.

Para que o PostgreSQL gere o log devemos configurar o arquivo postgresql.conf. O arquivo se encontra no seguinte caminho do servidor em que está instalado o banco de dados : C:\Program Files\PostgreSQL\10\data.

Após acessar está pasta Edite o arquivo mencionado anteriormente, você pode utilizar o bloco de notas ou um editor como o Notepad++.

Ao editar procure pelos seguintes comando e preencha da seguinte forma:
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'
logging_collector  = on

**OBSERVAÇÃO: Caso esses parâmetros estejam com # na frente do nome, isso significa que eles estão comentados e o postgreSQL não irá executar eles, após descomentar basta salvar o arquivo.

Feito estes procedimentos basta iniciar novamente os serviços do PostgreSQL e na pasta Log ele irá gerar o arquivo atualizando ele com os comando executados no sistema. Ele irá gerar log até dos comando que são executadas em ferramenta de terceiro, qualquer interação ele irá logar.

Operações Join

Usamos as operações JOIN para relacionar dados de duas ou mais tabelas em uma consulta, utilizando igualdade de colunas em comum ou não. Será mostrado um exemplo de situação onde pode ser usado os diferentes tipos de JOIN e seus resultados entre duas tabelas.

TABELAS EXEMPLO:

EX_ESTADO

| UF | DESCRICAO      |
-----------------------
| SC | Santa Catarina |
| SP | São Paulo      |

SQL de CRIAÇÃO/INSERÇÃO:

CREATE TABLE ex_estado (uf VARCHAR(2), descricao TEXT);
INSERT INTO ex_estado VALUES ('SC', 'Santa Catarina'),
                             ('SP', 'São Paulo');
COMMIT;

EX_CIDADE

| IDCIDADE | NOME          | UF |
---------------------------------
|        1 | Criciúma      | SC |
|        2 | Florianópolis | SC |
|        3 | Curitiba      | PR |

SQL de CRIAÇÃO/INSERÇÃO:

CREATE TABLE ex_cidade (idcidade INTEGER, nome TEXT, uf VARCHAR(2));
INSERT INTO ex_cidade VALUES (1, 'Criciúma', 'SC'),
                             (2, 'Florianópolis', 'SC'),
                             (3, 'Curitiba', 'PR');
COMMIT;

INNER JOIN

É usado para relacionar e mostrar os dados de ambas as tabelas quando houver igualdade nos campos em comum.

SELECT ES.DESCRICAO AS ESTADO, 
       CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
 INNER JOIN EX_CIDADE CI ON ES.UF = CI.UF

Usos alternativos que retornam o mesmo resultado:

Não é necessário informar a operação INNER.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
  JOIN EX_CIDADE CI ON ES.UF = CI.UF

Utilizando com a operação NATURAL. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas

 SELECT ES.DESCRICAO AS ESTADO,
             CI.NOME AS CIDADE 
   FROM EX_ESTADO ES 
NATURAL JOIN EX_CIDADE CI

Utilizando com a cláusula USING. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas

SELECT es.descricao as estado, 
            ci.nome as cidade 
  FROM ex_estado es 
  JOIN ex_cidade ci USING (uf)

Utilizando com a cláusula WHERE.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES, EX_CIDADE CI 
 WHERE ES.UF = CI.UF

Retorno:

| ESTADO         | CIDADE        |
----------------------------------
| Santa Catarina | Criciúma      |
| Santa Catarina | Florianópolis |

LEFT OUTER JOIN

É usado para relacionar e mostrar os dados de ambas as tabelas, preservando somente os dados da primeira tabela mesmo que não haja igualdade nos campos em comum.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
  LEFT OUTER JOIN EX_CIDADE CI ON ES.UF = CI.UF

Usos alternativos que retornam o mesmo resultado:

Não é necessário informar a operação OUTER.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
  LEFT JOIN EX_CIDADE CI ON ES.UF = CI.UF

Utilizando com a operação NATURAL. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES NATURAL LEFT JOIN EX_CIDADE CI

Utilizando com a cláusula USING. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
  LEFT JOIN EX_CIDADE CI USING (UF)

Retorno:

| ESTADO         | CIDADE        |
----------------------------------
| Santa Catarina | Criciúma      |
| Santa Catarina | Florianópolis |
| São Paulo      | (null)        |

RIGHT OUTER JOIN

É usado para relacionar e mostrar os dados de ambas as tabelas, preservando somente os dados da segunda tabela mesmo que não haja igualdade nos campos em comum.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
 RIGHT OUTER JOIN EX_CIDADE CI ON ES.UF = CI.UF

Usos alternativos que retornam o mesmo resultado:

Não é necessário informar a operação OUTER.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
 RIGHT JOIN EX_CIDADE CI ON ES.UF = CI.UF

Utilizando com a operação NATURAL. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES NATURAL RIGHT JOIN EX_CIDADE CI

Utilizando com a cláusula USING. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
 RIGHT JOIN EX_CIDADE CI USING (UF)

Retorno:

| ESTADO         | CIDADE        |
----------------------------------
| Santa Catarina | Florianópolis |
| Santa Catarina | Criciúma      |
| (null)         | Curitiba      |

FULL OUTER JOIN

É usado para relacionar e mostrar os dados de ambas as tabelas, preservando os dados mesmo que não haja igualdade nos campos em comum.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
  FULL OUTER JOIN EX_CIDADE CI ON ES.UF = CI.UF

Usos alternativos que retornam o mesmo resultado:

Não é necessário informar a operação OUTER.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES 
  FULL JOIN EX_CIDADE CI ON ES.UF = CI.UF

Utilizando com a operação NATURAL. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES NATURAL FULL JOIN EX_CIDADE CI

Utilizando com a cláusula USING. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES FULL JOIN EX_CIDADE CI USING (UF)

Retorno:

| ESTADO         | CIDADE        |
----------------------------------
| Santa Catarina | Criciúma      |
| Santa Catarina | Florianópolis |
| (null)         | Curitiba      |
| São Paulo      | (null)        |

CROSS JOIN

É usado para mostrar os dados de ambas as tabelas sem relacionar por campos em comum. Essa operação relaciona todos registros de uma tabela com todos registros da outra tabela.

SELECT ES.DESCRICAO AS ESTADO, 
            CI.NOME AS CIDADE 
  FROM EX_ESTADO ES CROSS JOIN EX_CIDADE CI

Retorno:

| ESTADO         | CIDADE        |
----------------------------------
| Santa Catarina | Criciúma      |
| Santa Catarina | Florianópolis |
| Santa Catarina | Curitiba      |
| São Paulo      | Criciúma      |
| São Paulo      | Florianópolis |
| São Paulo      | Curitiba      |

Order by, Group by e Having

Com os operadores ORDER BY, GROUP BY e HAVING podemos organizar nossas consultas mais dinamicamente.

TABELA EXEMPLO

EX_ITENS

| CODIGO | DESCRICAO      | TIPO     | QUANTIDADE |
---------------------------------------------------
|      1 | 'Biscoito'	  | 'Comida' |         20 |
|      2 | 'Água'         | 'Bebida' |      47,95 |
|      3 | 'Suco'         | 'Bebida' |       23,4 |
|      4 | 'Pão'          | 'Comida' |         10 |
|      5 | 'Refrigerante' | 'Bebida' |      13,75 |

SQL de CRIAÇÂO/INSERÇÂO:

CREATE TABLE ex_itens (
  codigo INT4,
  descricao TEXT,
  tipo TEXT,
  quantidade DECIMAL(15,2));
INSERT INTO ex_itens VALUES (1, 'Biscoito', 'Comida', 20),
                            (2, 'Água', 'Bebida', 47.95),
                            (3, 'Suco', 'Bebida', 23.4),
                            (4, 'Pão', 'Comida', 10),
                            (5, 'Refrigerante', 'Bebida', 13.75);
COMMIT;

ORDER BY

Ordenar crescente pela coluna TIPO.

SELECT * FROM ex_itens ORDER BY tipo

Retorno:

| CODIGO | DESCRICAO      | TIPO     | QUANTIDADE |
---------------------------------------------------
|      3 | 'Suco'         | 'Bebida' |       23,4 |
|      2 | 'Água'         | 'Bebida' |      47,95 |
|      5 | 'Refrigerante' | 'Bebida' |      13,75 |
|      1 | 'Biscoito'	  | 'Comida' |         20 |
|      4 | 'Pão'          | 'Comida' |         10 |

Ordenar crescente pela coluna TIPO e decrescente pela coluna QUANTIDADE. (a ordenação respeita a sequência inserida).

SELECT * FROM ex_itens ORDER BY tipo, quantidade desc

Retorno:

| CODIGO | DESCRICAO      | TIPO     | QUANTIDADE |
---------------------------------------------------
|      2 | 'Água'         | 'Bebida' |      47,95 |
|      3 | 'Suco'         | 'Bebida' |       23,4 |
|      5 | 'Refrigerante' | 'Bebida' |      13,75 |
|      1 | 'Biscoito'	  | 'Comida' |         20 |
|      4 | 'Pão'          | 'Comida' |         10 |

GROUP BY

Agrupar dados pela coluna TIPO contando a quantidade de itens (linhas) e volumes (coluna QUANTIDADE) para cada tipo de item.

SELECT tipo, count(1) as qtd_item, SUM(quantidade) as qtd_volume FROM ex_itens GROUP BY tipo

Retorno:

| TIPO     | QTD_ITEM | QTD_VOLUME |
------------------------------------
| 'Bebida' |        3 |       85,1 |
| 'Comida' |        2 |         30 |

HAVING

Agrupar pela coluna TIPO mostrando somente quando a soma da coluna QUANTIDADE é menor do que 40.

SELECT tipo, sum(quantidade) as qtd_volume 
  FROM ex_itens
 GROUP BY tipo HAVING SUM(quantidade) < 40

Retorno:

| TIPO     | QTD_VOLUME |
-------------------------
| 'Comida' |         30 |

Agrupar pela coluna TIPO mostrando somente quando a contagens de linhas é maior ou igual à 3.

SELECT tipo, count(1) as qtd_item 
  FROM ex_itens
 GROUP BY tipo HAVING COUNT(1) >= 3

Retorno:

| TIPO     | QTD_ITEM |
-----------------------
| 'Bebida' |        3 |

Pacote de Funções DOX (Schema) - PostgreSQL

Pensando em facilitar a busca de informações dentro do software por meio de instruções SQL, ao longo do tempo foram criadas várias funções e procedures que simplificaram ainda mais a busca de informações, seja por nossos consultores internos, ou por nossos clientes em suas automações.

A Ema disponibiliza um schema / package para criar no banco de dados que terá todas as funções e procedures.

Para isso, basta executar os scripts abaixo para o banco especificado:

Primeiramente, executar o script para criação do schema

create schema pkg_ema authorization postgres; grant all on schema pkg_ema to postgres with grant option;

Em seguida, executar a criação das funções

CREATE OR REPLACE FUNCTION pkg_ema.retorna_lista(in_lista text, delimitador character)
 RETURNS TABLE(out_id integer, out_str text)
AS $body$
declare
  dados record;
begin
IN_LISTA = REPLACE(REPLACE(IN_LISTA,'(',''),')','');
   
   IF DELIMITADOR = '' OR DELIMITADOR IS NULL THEN DELIMITADOR = ','; END IF;
  IF IN_LISTA <> '.' and IN_LISTA is not null and IN_LISTA <> '' and (IN_LISTA not like '/*%' and IN_LISTA not like '%*/')  THEN   
  for dados in (select unnest(string_to_array(IN_LISTA, DELIMITADOR)) LISTA) 
  LOOP
  BEGIN
  OUT_ID  = cast(dados.lista as integer);
     OUT_STR = dados.lista;
  RETURN NEXT;
 
   EXCEPTION
WHEN OTHERS then
   OUT_ID = 0;
OUT_STR = dados.lista; 
    RETURN NEXT;
  END;
  END LOOP; 
  ELSE 
OUT_ID  = 0;
  OUT_STR = '0';
  RETURN NEXT;
END IF;
    
end;
$body$
language plpgsql volatile cost 100;
alter function pkg_ema.retorna_lista(text, character) owner to postgres;

create or replace function pkg_ema.retorna_valor_variavel(xidprocesso in int4, xidvariavel in int4) returns varchar as
$body$
  declare
  valor varchar(5000);
  begin
  select valoratual into valor
  from public.crm_processo_variavel
  where crm_processo_variavel.idprocesso = xidprocesso
  and crm_processo_variavel.idvariavel = xidvariavel ;
  return valor;
  end;
$body$
language plpgsql volatile cost 100;
alter function pkg_ema.retorna_valor_variavel(int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_valor_variavel_texto(xidprocesso in int4, xidvariavel in int4) returns varchar as
$body$
  declare
  valor varchar(5000);
  begin
  select valoratual into valor
  from public.crm_processo_variavel
  where crm_processo_variavel.idprocesso = xidprocesso
  and crm_processo_variavel.idvariavel = xidvariavel ;
  return valor;
  end;
$body$
language plpgsql volatile cost 100;
alter function pkg_ema.retorna_valor_variavel_texto(int4, int4) owner to postgres;


CREATE OR REPLACE FUNCTION pkg_ema.retorna_valor_variavel_clob(xidprocesso integer, xidvariavel integer) RETURNS text as
$body$
  declare
  valor text;
  begin
  select valoratual into valor
  from public.crm_processo_variavel
  where crm_processo_variavel.idprocesso = xidprocesso
  and crm_processo_variavel.idvariavel = xidvariavel ;
  return trim(valor);
  end;
$body$
language plpgsql volatile cost 100;
alter function pkg_ema.retorna_valor_variavel_clob(int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_valor_variavel_int(xidprocesso in int4, xidvariavel in int4) returns int4 as
$body$
declare
 valor int4;
  begin
  select cast(case when valoratual = '.' then '0' else valoratual end as int4)
  into valor
  from public.crm_processo_variavel
  where crm_processo_variavel.idprocesso = xidprocesso
  and crm_processo_variavel.idvariavel = xidvariavel ;
  return valor;
  end;
$body$
language plpgsql volatile cost 100;
alter function pkg_ema.retorna_valor_variavel_int(int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_valor_variavel_valor(xidprocesso in int4, xidvariavel in int4) returns decimal(15,2) as
$body$
  declare
   valor decimal(15,2) ;
  begin
  select replace(replace(case when valoratual = '.' then '0' else valoratual end, '.', ''), ',', '.')
  into valor
  from public.crm_processo_variavel
  where crm_processo_variavel.idprocesso = xidprocesso
  and crm_processo_variavel.idvariavel = xidvariavel ;
  return valor;
  end;
$body$
language plpgsql volatile cost 100;
alter function pkg_ema.retorna_valor_variavel_valor(int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_valor_variavel_data(xidprocesso in int4, xidvariavel in int4) returns date as
$body$
  declare
   valor date;
  begin
  if (xidprocesso > 0 and xidvariavel > 0) then
  begin
  select case when valoratual <> '.' then to_date(valoratual,'dd/mm/yyyy HH24:MI')
   else to_date('01-01-1900','dd/mm/yyyy HH24:MI') end into valor
  from public.crm_processo_variavel
  where crm_processo_variavel.idprocesso = xidprocesso
  and crm_processo_variavel.idvariavel = xidvariavel ;
  end;
  else
  valor := to_date('01/01/1900','dd/mm/yyyy HH24:MI');
  end if;
  return valor;
  end;
$body$
language plpgsql volatile cost 100;
alter function pkg_ema.retorna_valor_variavel_data(int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_valor_variavel_datahr(xidprocesso in int4, xidvariavel in int4) returns timestamp as
$body$
  declare
   valor timestamp;
  begin
  if (xidprocesso > 0 and xidvariavel > 0) then
  begin
  select case when valoratual <> '.' then to_timestamp(valoratual,'dd/mm/yyyy HH24:MI')
   else to_timestamp('01-01-1900','dd/mm/yyyy HH24:MI') end into valor
  from public.crm_processo_variavel
  where crm_processo_variavel.idprocesso = xidprocesso
  and crm_processo_variavel.idvariavel = xidvariavel ;
  end;
  else
  valor := to_timestamp('01/01/1900','dd/mm/yyyy HH24:MI');
  end if;
  return valor;
  end;
$body$
language plpgsql volatile cost 100;
alter function pkg_ema.retorna_valor_variavel_datahr(int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_coluna_grade(xidprocesso int4, xidatividade int4, xidformulario int4, xidgrade int4, xidvalor int4)
 returns character varying as
 $body$
  declare
   leitura varchar(4000);
  begin
   select
    resposta into leitura
   from public.crm_processo_grade_valor
   where idprocesso = xidprocesso
    and idformulario = xidformulario
    and idatividade = xidatividade
    and idgrade  = xidgrade
    and idvalor  = xidvalor
    and idrepeticao = (select max(idrepeticao)
          from crm_processo_grade_valor xx
          where xx.idatividade = crm_processo_grade_valor.idatividade
          and xx.idprocesso = crm_processo_grade_valor.idprocesso
          and xx.idformulario = crm_processo_grade_valor.idformulario);
  return(trim(leitura));
  end;
 $body$
  language plpgsql volatile cost 100;
 alter function pkg_ema.retorna_coluna_grade(int4, int4, int4, int4, int4)owner to postgres;

create or replace function pkg_ema.retorna_coluna_grade_texto(xidprocesso int4, xidatividade int4, xidformulario int4, xidgrade int4, xidvalor int4)
 returns character varying as
 $body$
  declare
   leitura varchar(4000);
  begin
   select
    resposta into leitura
   from public.crm_processo_grade_valor
   where idprocesso = xidprocesso
    and idformulario = xidformulario
    and idatividade = xidatividade
    and idgrade  = xidgrade
    and idvalor  = xidvalor
    and idrepeticao = (select max(idrepeticao)
          from crm_processo_grade_valor xx
          where xx.idatividade = crm_processo_grade_valor.idatividade
          and xx.idprocesso = crm_processo_grade_valor.idprocesso
          and xx.idformulario = crm_processo_grade_valor.idformulario);
  return(trim(leitura));
  end;
 $body$
  language plpgsql volatile cost 100;
 alter function pkg_ema.retorna_coluna_grade_texto(int4, int4, int4, int4, int4)owner to postgres;

create or replace function pkg_ema.retorna_coluna_grade_clob(xidprocesso int4, xidatividade int4, xidformulario int4, xidgrade int4, xidvalor int4)
 returns text as
 $body$
  declare
   leitura text;
  begin
   select
    resposta into leitura
   from public.crm_processo_grade_valor
   where idprocesso = xidprocesso
    and idformulario = xidformulario
    and idatividade = xidatividade
    and idgrade  = xidgrade
    and idvalor  = xidvalor
    and idrepeticao = (select max(idrepeticao)
          from crm_processo_grade_valor xx
          where xx.idatividade = crm_processo_grade_valor.idatividade
          and xx.idprocesso = crm_processo_grade_valor.idprocesso
          and xx.idformulario = crm_processo_grade_valor.idformulario);
  return(trim(leitura));
  end;
 $body$
  language plpgsql volatile cost 100;
 alter function pkg_ema.retorna_coluna_grade_texto(int4, int4, int4, int4, int4)owner to postgres;

create or replace function pkg_ema.retorna_coluna_grade_int(xidprocesso int4, xidatividade int4, xidformulario int4, xidgrade int4, xidvalor int4)
 returns int4 as
 $body$
 declare
  leitura int4;
 
  begin 
  /*leitura := 0 ;
  select (select to_number(decode(respostainteiro,'.',0,respostainteiro)) from dual)*/
 
  select respostainteiro into leitura
  from public.crm_processo_grade_valor
  where idprocesso = xidprocesso
  and idformulario = xidformulario
  and idatividade = xidatividade
  and idgrade = xidgrade
  and idvalor = xidvalor
  and idrepeticao = (select max(idrepeticao)
       from crm_processo_grade_valor xx
       where xx.idatividade = crm_processo_grade_valor.idatividade
       and xx.idprocesso = crm_processo_grade_valor.idprocesso);
 
  return(leitura);
 
  end;
 $body$
 language plpgsql volatile cost 100;
 alter function pkg_ema.retorna_coluna_grade_int(int4, int4, int4, int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_coluna_grade_valor(xidprocesso int4, xidatividade int4, xidformulario int4, xidgrade int4, xidvalor int4)
 returns numeric(15,2) as
 $body$
  declare
   leitura numeric(15,2);
  begin  
   select respostavalor into leitura
   from public.crm_processo_grade_valor
   where idprocesso  = xidprocesso
    and idformulario  = xidformulario
    and idatividade  = xidatividade
    and idgrade   = xidgrade
    and idvalor   = xidvalor
    and idrepeticao  = (select max(idrepeticao)
           from crm_processo_grade_valor xx
           where xx.idatividade = crm_processo_grade_valor.idatividade
            and xx.idprocesso  = crm_processo_grade_valor.idprocesso);
   return(leitura);
  end;
 $body$
  language plpgsql volatile cost 100;
  alter function pkg_ema.retorna_coluna_grade_valor(int4, int4, int4, int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_coluna_grade_data( xidprocesso int4, xidatividade int4, xidformulario int4, xidgrade int4, xidvalor int4)
 returns date as
 $body$
  declare
   leitura date;
  begin
   select respostadatahora into leitura
   from public.crm_processo_grade_valor
   where idprocesso = xidprocesso
    and idformulario= xidformulario
    and idatividade = xidatividade
    and idgrade  = xidgrade
    and idvalor  = xidvalor
    and idrepeticao = (select max(idrepeticao)
         from crm_processo_grade_valor xx
         where xx.idatividade = crm_processo_grade_valor.idatividade
          and xx.idprocesso  = crm_processo_grade_valor.idprocesso
          and xx.idformulario = crm_processo_grade_valor.idformulario);
  return(leitura); --fazer o trim depois
  
  end;
 $body$
 
 language plpgsql volatile cost 100;
 alter function pkg_ema.retorna_coluna_grade_data(int4, int4, int4, int4, int4) owner to postgres;

create or replace function pkg_ema.retorna_coluna_grade_datahr( xidprocesso int4, xidatividade int4, xidformulario int4, xidgrade int4, xidvalor int4)
 returns timestamp as
 $body$
  declare
   leitura timestamp;
  begin
   select respostadatahora into leitura
   from public.crm_processo_grade_valor
   where idprocesso = xidprocesso
    and idformulario= xidformulario
    and idatividade = xidatividade
    and idgrade  = xidgrade
    and idvalor  = xidvalor
    and idrepeticao = (select max(idrepeticao)
         from crm_processo_grade_valor xx
         where xx.idatividade = crm_processo_grade_valor.idatividade
          and xx.idprocesso  = crm_processo_grade_valor.idprocesso
          and xx.idformulario = crm_processo_grade_valor.idformulario);
  return(leitura);
  
  end;
 $body$ 
 language plpgsql volatile cost 100;
 alter function pkg_ema.retorna_coluna_grade_datahr(int4, int4, int4, int4, int4) owner to postgres;

Chamando as Funções

Retorna lista

	RETORNO
SQL	OUT_ID (Saída Integer)	OUT_STR (Saída Text)
select * from pkg_ema.retorna_lista('/IDCLIENTE/',',');	0	0
select * from pkg_ema.retorna_lista('0',',');	0	0
select * from pkg_ema.retorna_lista('','');	0	0
select * from pkg_ema.retorna_lista('(9,7)',',');	9 7	9 7
select * from pkg_ema.retorna_lista('(10;74)',';');	10 74	10 74
select * from pkg_ema.retorna_lista('(91:5:S:SS)',':');	91 5 0 0	91 5 S SS
select * from pkg_ema.retorna_lista('(ASD,ASDA,ASDA)',',');	0 0 0	ASD ASDA ASDA

Variável:

select pkg_ema.retorna_valor_variavel(idprocesso, 1) as padrao,
       pkg_ema.retorna_valor_variavel_texto(idprocesso, 2) as texto,
       pkg_ema.retorna_valor_variavel_int(idprocesso, 3) as inteiro,
       pkg_ema.retorna_valor_variavel_valor(idprocesso, 4) as valor,
       pkg_ema.retorna_valor_variavel_data(idprocesso, 5) as data,
       pkg_ema.retorna_valor_variavel_datahr(idprocesso, 6) as datahr,
       pkg_ema.retorna_valor_variavel_clob(idprocesso, 7) as clob
from crm_processo where idprocesso = 0/*IDPROCESSO*/

Grade:

select pkg_ema.retorna_coluna_grade(x.idprocesso, x.idatividade, x.idformulario, 1, x.idvalor) as padrao,
       pkg_ema.retorna_coluna_grade_texto(x.idprocesso, x.idatividade, x.idformulario, 2, x.idvalor) as texto,
       pkg_ema.retorna_coluna_grade_int(x.idprocesso, x.idatividade, x.idformulario, 3, x.idvalor) as inteiro,
       pkg_ema.retorna_coluna_grade_valor(x.idprocesso, x.idatividade, x.idformulario, 4, x.idvalor) as valor,
       pkg_ema.retorna_coluna_grade_data(x.idprocesso, x.idatividade, x.idformulario, 5, x.idvalor) as data,
       pkg_ema.retorna_coluna_grade_datahr(x.idprocesso, x.idatividade, x.idformulario, 6, x.idvalor) as datahr,
       pkg_ema.retorna_coluna_grade_clob(x.idprocesso, x.idatividade, x.idformulario, 7, x.idvalor) as clob
from crm_processo_grade_valor x
where idprocesso = 0/*IDPROCESSO*/
  and idatividade = 0/*IDATIVIDADE*/
  and idformulario = 0/*IDFORMULARIO*/
  and idrepeticao = (select max(idrepeticao) 
                     from crm_processo_grade_valor 
                     where idprocesso = x.idprocesso 
                     and idatividade = x.idatividade) 
  and idgrade = 1 order by idvalor

Resultados do SELECT separados por virgula

Estou compartilhando um recurso muito útil e nativo do postgres, assim como utilizamos o famoso RETORNA LISTA que o postgres também possui uma função nativa, e pode ser visualizado no link abaixo:

clique aqui.

Podemos nos deparar com a necessidade de retornar resultados de um SELECT separados/concatenados por virgulas por exemplo, ou qualquer outro delimitador, abaixo veremos o comando em um exemplo bem genérico, e sua explicação:

Exemplo de uso:

SELECT ARRAY_TO_STRING(ARRAY(SELECT IDUSUARIO
                               FROM USUARIO_PAPELFUNCAO
                              WHERE IDPAPELFUNCAO = 25), ',')
  FROM VERSAODB

Ou seja, perceba que nosso SELECT principal esta dentro de um ARRAY(Conjunto de dados ou Matriz), e logo após utilizamos o recurso no postgres ARRAY_TO_STRING que transforma o ARRAY criado em uma string, e informamos neste comando que nosso limitador será a virgula, podendo ser alterado conforme sua necessidade.

Retorno obtido:

31,2,36,12

Caso de uso:

Vamos supor que eu precisa de todos os usuários do papel função 25, já que somente esses podem colaborar em uma atividade em questão, porém, segundo levantado o usuário o qual iniciou o processo também poderá colaborar, sabendo que ele pode não ter o papel função 25, salvamos o Username deste usuário na variável /*USUARIO*/

SELECT ARRAY_TO_STRING(ARRAY(SELECT IDUSUARIO
                               FROM USUARIO_PAPELFUNCAO
                              WHERE IDPAPELFUNCAO = 25
                             
                             UNION ALL

                              SELECT IDUSUARIO
                                FROM USUARIO
                              WHERE USERNAME = '/*USUARIO*/'), ',')
FROM VERSAODB

Perceba que neste caso, optei por fazer um union all dentro do array, assim o resultado será convertido e concatenado ao conjunto de resultado, existem outras formas de atender essa demanda, espero que tenha ajudado e que você possa adaptar os exemplos acima em seus processos / SQLs.

Retorna lista

Código SQL a ser usado:

select unnest(string_to_array('1,2,3', ',')) as lista

Retorno:

| LISTA |
---------
| 1     |
| 2     |
| 3     |

Tamanhos da tabelas (MB)

Neste tópico mostraremos o comando SQL que retorna qual schema, tabela, e tamanho em MB de cada uma delas.

SELECT esquema, tabela,
       pg_size_pretty(pg_relation_size(esq_tab)) AS tamanho,
       pg_size_pretty(pg_total_relation_size(esq_tab)) AS tamanho_total
  FROM (SELECT tablename AS tabela,
               schemaname AS esquema,
               schemaname||'.'||tablename AS esq_tab
          FROM pg_catalog.pg_tables
         WHERE schemaname NOT
            IN ('pg_catalog', 'information_schema', 'pg_toast') ) AS x
 ORDER BY pg_total_relation_size(esq_tab) DESC;

Retorno do banco:

Union, Union All, Except, Except All e Intersect

É possível combinar os resultados de duas ou mais consultas através dos operadores Union, Except e Intersect. Será mostrado um exemplo de situação onde esses operadores podem ser usados e os resultados entre duas tabelas.

TABELAS EXEMPLO:

EX_FILIAL

| DESCRICAO  |
--------------
| 'Matriz'   |
| 'Matriz'   |
| 'Filial 1' |
| 'Filial 1' |
| 'Filial 2' |
| 'Filial 3' |

SQL de CRIAÇÃO/INSERÇÃO:

CREATE TABLE ex_filial (descricao TEXT);
INSERT INTO ex_filial VALUES ('Matriz'),
                             ('Matriz'),
                             ('Filial 1'),
                             ('Filial 1'),
                             ('Filial 2'),
                             ('Filial 3');
COMMIT;

EX_PONTOS_DE_VENDA

| DESCRICAO   |
---------------
| 'Filial 1'  |
| 'Filial 2'  |
| 'Filial 2'  |
| 'Filial 3'  |
| 'Unidade 1' |
| 'Unidade 1' |
| 'Unidade 2' |

SQL de CRIAÇÃO/INSERÇÃO:

CREATE TABLE ex_pontos_de_venda (descricao TEXT);
INSERT INTO ex_pontos_de_venda VALUES ('Filial 1'),
                                      ('Filial 2'),
                                      ('Filial 2'),
                                      ('Filial 3'),
                                      ('Unidade 1'),
                                      ('Unidade 1'),
                                      ('Unidade 2');
COMMIT;

UNION

O operador UNION une o retorno de duas consultas fazendo DISTINCT.

SELECT DESCRICAO FROM EX_FILIAL
 UNION
SELECT DESCRICAO FROM EX_PONTOS_DE_VENDA

Retorno:

| DESCRICAO   |
---------------
| 'Filial 2'  |
| 'Matriz'    |
| 'Unidade 2' |
| 'Unidade 1' |
| 'Filial 1'  |
| 'Filial 3'  |

UNION ALL

O operador UNION ALL une o retorno de duas consultas sem fazer DISTINCT.

SELECT DESCRICAO FROM EX_FILIAL
 UNION ALL
SELECT DESCRICAO FROM EX_PONTOS_DE_VENDA

Retorno:

| DESCRICAO   |
---------------
| 'Matriz'    |
| 'Matriz'    |
| 'Filial 1'  |
| 'Filial 1'  |
| 'Filial 2'  |
| 'Filial 3'  |
| 'Filial 1'  |
| 'Filial 2'  |
| 'Filial 2'  |
| 'Filial 3'  |
| 'Unidade 1' |
| 'Unidade 1' |
| 'Unidade 2' |

EXCEPT

O operador EXCEPT não mostra qualquer dado da primeira consulta que se repete na segunda.

SELECT DESCRICAO FROM EX_FILIAL
EXCEPT
SELECT DESCRICAO FROM EX_PONTOS_DE_VENDA

Retorno:

| DESCRICAO |
--------------
| 'Matriz'  |

EXCEPT ALL

O operador EXCEPT ALL elimina os dados da primeira consulta de acordo com a quantidade que o mesmo resultado se repete na segunda.

SELECT DESCRICAO FROM EX_FILIAL
EXCEPT ALL
SELECT DESCRICAO FROM EX_PONTOS_DE_VENDA

Retorno:

| DESCRICAO  |
---------------
| 'Filial 1' |
| 'Matriz'   |
| 'Matriz'   |

INTERSECT

O operador INTERSECT mostra somente os dados que contem em ambas as consultas.

SELECT DESCRICAO FROM EX_FILIAL
INTERSECT
SELECT DESCRICAO FROM EX_PONTOS_DE_VENDA

Retorno:

| DESCRICAO  |
--------------
| 'Filial 1' |
| 'Filial 2' |
| 'Filial 3' |

Utilização de Datas e Conversões (current_date, timestamp, to_char, etc)

CURRENT_DATE

Selecionada data atual

select current_date --> Retorno: "2019-09-13"

CURRENT_TIME

Seleciona hora atual

select current_time --> Retorno: "08:56:29.649098-03"

CURRENT_TIMESTAMP

Seleciona data e hora atual (funções alternativas: now() e transaction_timestamp())

select current_timestamp --> Retorno: "2019-09-13 08:56:29.649098-03"

OBS: Importante saber que o banco estará parametrizado para mostrar um valor de data/hora com um único formato para todos. Se a intenção é mostrar a data/hora em um formato diferente do parametrizado, é necessário converter a data/hora para um valor do tipo texto, definindo o formato desejado nas funções de conversão. Veja abaixo.

TO_CHAR

Pode ser usado para converter uma data/hora para texto no formato em que deseja visualizar.

select to_char(current_date, 'MM/YYYY') --> Retorno: "09/2019"

select to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS') --> Retorno: "13/09/2019 08:56:29"

select to_char(now(), 'HH24:MI') --> Retorno: "08:56"

select to_char(current_date, 'TMDay') --> Retorno: "Sexta-Feira"

select to_char(current_date, 'DD')||' de '||to_char(current_date, 'TMMonth')||' de '||to_char(current_date, 'YYYY') --> Retorno: "13 de Setembro de 2019"

TO_DATE e TO_TIMESTAMP

Usado para converter um valor texto para um valor data/hora.

select to_date('13/09/2019', 'DD/MM/YYYY') --> Retorno: "2019-09-13"

select to_timestamp('13/09/2019 08:51:43', 'DD/MM/YYYY HH24:MI:SS') --> Retorno:"2019-09-13 08:51:43-03"

EXTRACT

Pode ser usado para extrair uma informação específica de um campo de Data ou Data/hora.

select extract(millennium from now()) --> Retorno: 3 <-- (Milênio atual)

select extract(century from now()) --> Retorno: 21 <-- (século atual)

select extract(decade from now()) --> Retorno: 201 <-- (década atual)

select extract(year from now()) --> Retorno: 2019 <-- (ano atual)

select extract(month from now()) --> Retorno: 9 <-- (mês atual)

select extract(dow from now()) --> Retorno: 5 <-- (dia da semana atual)

select extract(day from now()) --> Retorno: 13 <-- (dia do mês atual)

select extract(hour from now())||':'||
	   extract(minute from now())||':'||
	   trunc(extract(second from now())) --> Retorno: 14:43:46 <-- (hora atual)

CAST

Função para fazer conversão de valores de diversos tipos. Neste caso, de data para texto de texto para data.

select cast(date '2019-09-13' as text) --> Retorno: "2019-09-13"

select cast('2019-09-13' as date) --> Retorno: "2019-09-13"

With, Substr, Split_part e Strpos

Neste exemplo será mostrado como utilizar as funções With, Substr e Split_part e Strpos combinadas. Vamos dividir em colunas, as informações de uma nota fiscal parametrizadas por posição.

Texto com as informações:

'NF 123456 - DT 07/10/2019 - VL 37,99'

SQL:

  WITH nota AS (SELECT 'NF 123456 - DT 07/10/2019 - VL 37,99' AS dados)
SELECT substr(split_part(dados, 'NF', 2), 2, strpos(split_part(dados, 'NF', 2), '-')-3) AS numeronf,
       substr(split_part(dados, 'DT', 2), 2, strpos(split_part(dados, 'DT', 2), '-')-3) AS dataemissao,
	   substr(split_part(dados, 'VL', 2), 2) AS valortotal
  FROM nota

Retorno:

| NUMERONF | DATAEMISSAO | VALORTOTAL |
---------------------------------------
| 123456   | 07/10/2019  | 37,99      |

Neste caso:

With: está sendo utilizado para que não seja preciso retornar o texto em todas as colunas, evitando repetir código e tempo de processamento;
Substr: está retornando dados a partir de uma posição até outra informadas do texto;
Split_part: retorna parte do texto a partir do caractere ou conjunto de caracteres informados;
Strpos: retorna a posição do texto, onde se encontra o caractere ou conjunto de caracteres informado, para usar a posição no Substr;

PostgreSQL

BACKUP - Resolução de problema com o pg_dump

Coalesce

TABELA EXEMPLO

COALESCE

Converter Binary Data (Bytea) para Text

Criação de View

Descobrir a maior versão de um procedimento

Descobrir SQL Executado

Erros de SQL comuns no banco PostgreSQL

Exportando consulta do banco de dados (Excel, TXT)

Exportando tabela em CSV (Excel)

Função - Formata CNPJ

Função - Formata CPF

Função - Primeiro e ultimo dia do mês

Função - Retorna parte de um campo de texto definindo inicio e fim

Função de agregação (count, sum, max, min, avg, partition by)

TABELA EXEMPLO

Consulta com agrupamento agregado:

Consulta com agrupamento analítico:

Função de Strings

Funções de data

1 - Funções básicas:

2 - Funções DATE_TRUNC:

3 - Exemplos de uso:

Gerar log dos comandos

Operações Join

TABELAS EXEMPLO:

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

Order by, Group by e Having

TABELA EXEMPLO

ORDER BY

GROUP BY

HAVING

Pacote de Funções DOX (Schema) - PostgreSQL

Primeiramente, executar o script para criação do schema

Em seguida, executar a criação das funções

Retorna lista

Variável:

Grade:

Resultados do SELECT separados por virgula

Retorna lista

Tamanhos da tabelas (MB)

Union, Union All, Except, Except All e Intersect

TABELAS EXEMPLO:

UNION

UNION ALL

EXCEPT

INTERSECT

Utilização de Datas e Conversões (current_date, timestamp, to_char, etc)

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

TO_CHAR

TO_DATE e TO_TIMESTAMP

EXTRACT

CAST

With, Substr, Split_part e Strpos