Exemplo de um script SQL interativo no PostgreSQL

A semana foi corrida, e o final de semana foi totalmente utilizado para descanso e lazer. Mas a vontade de escrever é maior, e cá estou novamente pronto para mais um post. A idéia hoje era falar sobre scripts SQL, então imaginei algo diferente, e pensei em mostrar como fazer um script interativo no PostgreSQL 8.3, associando o seu uso a um cenário hipotético. Vejamos…

Em um universo ideal, é muito comum existir um banco de dados de desenvolvimento, um de testes, um de homologação e um de produção. Neste cenário, imagine que seu Analista de Testes precise testar uma nova funcionalidade do sistema relacionada ao módulo de compras, e que para isso constantemente você tenha que aplicar a carga de testes desse módulo no banco de dados de teste, não é de se estranhar que você possua uma carga pronta (é o mínimo que você deveria ter).

Veja a seguir o script simplificado que representa esse cenário:

CREATE TABLE tb_cliente (
     nr_documento numeric(14,0) NOT NULL,
     nm_cliente character varying(60)
);
ALTER TABLE blog.tb_cliente OWNER TO postgres;

CREATE TABLE tb_compra (
     nr_nota_fiscal numeric(15,0) NOT NULL,
     dt_compra date,
     nr_documento_cliente numeric(14,0)
);
ALTER TABLE blog.tb_compra OWNER TO postgres;

CREATE TABLE tb_item_compra (
     nr_nota_fiscal numeric(15,0) NOT NULL,
     nr_referencia integer NOT NULL,
     qt_item smallint DEFAULT 1
);
ALTER TABLE blog.tb_item_compra OWNER TO postgres;

CREATE TABLE tb_produto (
     nr_referencia integer NOT NULL,
     ds_produto character varying(60) NOT NULL,
     vl_venda numeric(15,2)
);
ALTER TABLE blog.tb_produto OWNER TO postgres;

CREATE SEQUENCE tb_produto_nr_referencia_seq
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
ALTER TABLE blog.tb_produto_nr_referencia_seq OWNER TO postgres;

ALTER SEQUENCE tb_produto_nr_referencia_seq OWNED BY tb_produto.nr_referencia
ALTER TABLE tb_produto ALTER COLUMN nr_referencia SET DEFAULT nextval('tb_produto_nr_referencia_seq'::regclass);

ALTER TABLE ONLY tb_compra
     ADD CONSTRAINT pk_compra PRIMARY KEY (nr_nota_fiscal);
ALTER TABLE ONLY tb_item_compra
     ADD CONSTRAINT pk_item_compra PRIMARY KEY (nr_nota_fiscal, nr_referencia);
ALTER TABLE ONLY tb_cliente
     ADD CONSTRAINT pk_pessoa PRIMARY KEY (nr_documento);
ALTER TABLE ONLY tb_produt
     ADD CONSTRAINT pk_produto PRIMARY KEY (nr_referencia);

ALTER TABLE ONLY tb_compra
     ADD CONSTRAINT fk_cliente_compra_01 FOREIGN KEY (nr_documento_cliente) REFERENCES tb_cliente(nr_documento);
ALTER TABLE ONLY tb_item_compra
     ADD CONSTRAINT fk_compra_item_compra_01 FOREIGN KEY (nr_nota_fiscal) REFERENCES tb_compra(nr_nota_fiscal);
ALTER TABLE ONLY tb_item_compr
     ADD CONSTRAINT fk_produto_item_compra_01 FOREIGN KEY (nr_referencia) REFERENCES tb_produto(nr_referencia);

Esse é o conteúdo simplificado da entidade cliente no banco de dados de teste:

 nr_documento |        nm_cliente
--------------+---------------------------
74727756632 | Marta Antonia
56548986527 | Antonia Josefina
47040567970 | Adamantina Pereira
24348435149 | Carlos Augusto
80987468692 | José Silveira
56096344407 | Maria Eleontina de Castro
79056669606 | José da Silva Sauro
31887461081 | Ribamar de Castr
45792555043 | Manoel Bandeira

Esse é o conteudo simplificado da entidade produto no banco de dados de teste:

 nr_referencia |    ds_produto     | vl_venda
---------------+-------------------+----------
1 | Sapato Velho      |    15.00
2 | Sapato Novo       |    25.00
3 | Blusa Velha       |    35.00
4 | Blusa Nova        |    45.00
5 | Calça Jeans Velha |    29.50
6 | Calça Jeans Nova  |    49.50
7 | Peruca Masculina  |   439.9
8 | Camisola          |    19.50

E este é o Script de Carga de Teste para as entidades tb_compra e tb_item_compra:

postgres@banco $ cat ~/scripts/carga_compra_teste.sql
/***************************************************
*
* Script de geração de carga
* Modulo de Compras - Banco de dados de Teste
*
* (c) 2007 Dickson Guedes <guediz at gmail dot com>
*
****************************************************/

INSERT INTO tb_compra (nr_nota_fiscal, dt_compra, nr_documento_cliente
     VALUES (1234567890, now(), 56473847366);

INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
     VALUES (1234567890, 6,  2);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
     VALUES (1234567890, 4,  1);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
     VALUES (1234567890, 3, 15);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item
     VALUES (1234567890, 2,  2);

INSERT INTO tb_compra (nr_nota_fiscal, dt_compra, nr_documento_cliente
     VALUES (1122334455, now(), 56096344407);

INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
     VALUES (1122334455, 6,  2);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
     VALUES (1122334455, 4,  1);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
     VALUES (1122334455, 3, 15);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item
     VALUES (1122334455, 2,  2);

E para executá-lo e utilizo o utilitário psql:

postgresql@banco $ psql bdtesteWelcome to psql 8.4devel, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
     \h for help with SQL commands
     \? for help with psql commands
     \g or terminate with semicolon to execute quer
     \q to quit

postgres=# \i ~/scripts/carga_compra_teste.sql

E se eu quisesse tornar esse script interativo?

O utilitário psql além de aceitar instruções SQL, possui um conjunto de instrucoes proprias que nos permitem extenter suas funcionalidades. Utilizaremos duas delas: \echo, que permite enviar um texto para a console do psql e \prompt (a partir da versão 8.3 do PostgreSQL) que permite receber um dado da entrada e salvar um uma variável.

Nosso script acima poderia ficar assim:

/***************************************************
*
* Script de geração de carga
* Modulo de Compras - Banco de dados de Teste
*
* (c) 2007 Dickson Guedes <guediz at gmail dot com>
*
****************************************************/

\echo """"""""""""""""""""""""""""""""""""""""""
\echo " Gerando carga para o ambiente: TESTE   "
\echo "                                        "
\echo " Cenário: Prestacoes de compra a prazo  "
\echo "                                        "
\echo """"""""""""""""""""""""""""""""""""""""""
\echo
\echo Continuar..: Enter
\echo Cancelar...: Ctrl+C

\prompt continua
\unset continuar

\echo
SELECT nr_documento || ' -> ' || nm_cliente AS "Lista de Clientes" FROM tb_cliente;
\echo
\prompt 'Informe um dos números de CPFs acima.: ' v_nr_document
\prompt 'Informe o número da nota fiscal......: ' v_nr_nota_fiscal

\echo '* Inserindo compra para o cliente:' :v_nr_documento 'com número de nota fiscal:' :v_nr_nota_fiscal

INSERT INTO tb_compra (nr_nota_fiscal, dt_compra, nr_documento_cliente) VALUE
     (:v_nr_nota_fiscal, now(), :v_nr_documento);

INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item) VALUES
     (:v_nr_nota_fiscal, 2, ((RANDOM()*5)+1)::int2),
     (:v_nr_nota_fiscal, 4, ((RANDOM()*5)+1)::int2),
     (:v_nr_nota_fiscal, 7, ((RANDOM()*5)+1)::int2),
     (:v_nr_nota_fiscal, 5, ((RANDOM()*5)+1)::int2);

\echo "* Script finalizado!"

E para executá-lo e utilizo também o utilitário psql:

postgresql@banco $ psql bdtesteWelcome to psql 8.4devel, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute quer
\q to quit

postgres=# \i ~/scripts/carga_compra_teste.sql

DICA 1: quando usar o \prompt você pode usar 2 (dois) argumentos: um texto explicativo seguido do nome da variável que você deseja armazenar o valor recebido.
DICA 2: os valores das variáveis são recuperados utilizando-se o dois pontos (”:”) precedendo o nome da variável que se deseja ler o valor, como por exemplo \echo ‘Valor da variavel é’ :variavel_qualquer.

DESAFIO 1: Tente alterar o script acima para que os produtos também sejam aleatórios, ou seja, cada vez que o script for executado ele gere uma carga diferente.

DESAFIO 2: Como você trataria o problema de um erro de digitação ou problema de violação de chaves? Você poderia utilizar transações?