psql com extensao SQL

Todo mundo sabe o quão poderoso é o console interativo do psql.

Sinceramente não conheço nenhuma ferramenta que se iguale em qualquer plataforma em relação a funcionalidades (é lógico que os consoles irb1.8 e python também chegam bem próximo, mas para propósitos distintos).

A parte chata é quando voce digita linhas e linhas de consulta e toda vez que precisa alterar com o meta comando “\e” voce se depara com a necessidade de digitar :se syn=sql (vim). Lembro que vimrc não adianta por que ele considera a extensão do arquivo.

Não consegui fazer o vim (ou outro editor) converter o número do final do arquivo temporario do psql (tmp/psql.edit.9999) para syntax=sql e mesmo que conseguisse precisaria ainda assim fazer set de todos os plugins assistentes que são carregados em tempo de execução a partir da extensão .sql (diga-se de passagem, eu tenho vários plugins).

Então precisei alterar apenas uma linha do source do psql para incluir o sufixo .sql no nome do arquivo temporário. Imagino que se for para propor um patch para o PGDG, deveria ser criado uma variavel \pset para especificar qual a extensão do arquivo, mas aí a história já é outra…

Segue o patch:

Index: command.c
===================================================================
RCS file: /home/leo/pgrepo/pgsql/src/bin/psql/command.c,v
retrieving revision 1.186
diff -c -r1.186 command.c
*** command.c 1 Jan 2008 19:45:55 -0000 1.186
--- command.c 20 Feb 2008 20:37:40 -0000
***************
*** 1362,1371 ****
*/
#endif
#ifndef WIN32
! snprintf(fnametmp, sizeof(fnametmp), "%s%spsql.edit.%d", tmpdir,
"/", (int) getpid());
#else
! snprintf(fnametmp, sizeof(fnametmp), "%s%spsql.edit.%d", tmpdir,
"" /* trailing separator already present */ , (int) getpid());
#endif

— 1362,1371 —-
*/
#endif
#ifndef WIN32
! snprintf(fnametmp, sizeof(fnametmp), “%s%spsql.edit.%d.sql”, tmpdir,
“/”, (int) getpid());
#else
! snprintf(fnametmp, sizeof(fnametmp), “%s%spsql.edit.%d.sql”, tmpdir,
“” /* trailing separator already present */ , (int) getpid());
#endif

plperl uncrosstab (descruzamento de tabelas)

Alguns já devem conhecer no módulo contrib/tablefunc as funções de cruzamento de tabelas (crosstab*). Para aqueles que não conhecem trata-se de módulos para manipular dados de referência cruzada, utilizado principalmente para manipular a dimensão dos dados.

A idéia é inversa ao objetivo do crosstab.

A estrutura da tabela é a seguinte:

+--------+---------+-----------------------------------------------------+
| Coluna |  Tipo   |                    Modificadores                    |
+--------+---------+-----------------------------------------------------+
| id     | integer | not null default nextval('carros_id_seq'::regclass) |
| dono   | text    |                                                     |
| marca  | text    |                                                     |
| modelo | text    |                                                     |
| ano    | text    |                                                     |
| placa  | text    |                                                     |
+--------+---------+-----------------------------------------------------+

e a tabela foi populada como segue:

                       carros
+----+----------+--------+--------+------+---------+
| id |   dono   | marca  | modelo | ano  |  placa  |
+----+----------+--------+--------+------+---------+
|  1 | Leo      | Corsa  | 2006   | 2006 | BBB9999 |
|  2 | Leonardo | C3     | 2006   | 2008 | AAA1111 |
|  3 | Foo      | Audi   | 2008   | 2008 | XXX1234 |
|  4 | Bar      | Belina | 1970   | 1971 | XXX1234 |
+----+----------+--------+--------+------+---------+
(4 registros)

A brincadeira agora seria inverter a ordem dos registros, de forma que as colunas passassem a gerar um produto cartesiano com de forma que o atributo “dono” passe a ser cabeçalho de linha.

A versão em plpgsql ficou assim:

CREATE OR REPLACE FUNCTION reorganiza()
RETURNS SETOF carros_inverso
LANGUAGE 'PLPGSQL'
AS $_$
DECLARE
       vRecordSet carros%ROWTYPE;
       vNovaMatriz      carros_inverso%ROWTYPE;
       columns varchar[] := '{"marca","modelo","ano","placa"}';
       i RECORD;
BEGIN
       FOR vRecordSet IN SELECT * FROM carros LOOP
               vNovaMatriz.dono := vRecordSet.dono;
               FOR i IN 1..array_upper(columns,1) LOOP
                               IF columns[i] = 'marca' THEN
                                       vNovaMatriz.valor := vRecordSet.marca;
                               ELSIF columns[i] = 'modelo' THEN
                                       vNovaMatriz.valor := vRecordSet.modelo;
                               ELSIF columns[i] = 'ano' THEN
                                       vNovaMatriz.valor := vRecordSet.ano;
                               ELSIF columns[i] = 'placa' THEN
                                       vNovaMatriz.valor := vRecordSet.placa;
                               END IF;
                       RETURN NEXT vNovaMatriz;
               END LOOP;
       END LOOP;
END;
$_$
;

Esquisito, né? Eu também achei…
Ficou ruim para dar manutenção e restrita há um tipo de estrutura (tabela) apenas. Porém existem limitações da plpgsql que nos impedem de ir muito mais além disso, embora ainda seja possível melhorar um pouco. Mas deixemos plpgsql e vamos ver um modelo construído com as facilidades de plperl:

CREATE OR REPLACE FUNCTION reorganiza_p(IN colunas varchar,IN varchar, OUT nome TEXT, OUT valor TEXT)
RETURNS SETOF RECORD
LANGUAGE 'plperl'
AS $$
       my ($c,$s) = @_;
       my @columns = split(',',$c);
       my $heap;
       $_SHARED{nova_lista} = \$heap;

       my $sql = sprintf('SELECT %s FROM %s',join(',',@columns),$s);
       $_SHARED{exec_plan} = spi_prepare($sql);
       my $sth = spi_query_prepared($_SHARED{exec_plan});

       while (defined (my $row = spi_fetchrow($sth))){
               foreach(@columns[1..$#columns]){
                       push @$heap, {nome =>$row->{@columns[0]},valor => $row->{$_}}
               }
       }
       return ${$_SHARED{nova_lista}};
$$;

Hmm … ganhamos em alguns aspectos: Conseguimos fazer um modelo dinâmico que trabalha com qualquer tabela. Experimente, chame a função passando como argumentos uma string contendo todos os campos que se deseja colocar na formação cartesiana (o primeiro atributo da string será o cabeçalho de linha). O segundo parametro é o nome da tabela à que se quer fazer a reorganização (ou baderna …).

Veja o exemplo de utilização:
SELECT * from reorganiza_p(‘dono,marca,modelo,ano,placa’,'carros’);

        carros
+----------+---------+
|   nome   |  valor  |
+----------+---------+
| Leo      | Corsa   |
| Leo      | 2006    |
| Leo      | 2006    |
| Leo      | BBB9999 |
| Leonardo | C3      |
| Leonardo | 2006    |
| Leonardo | 2008    |
| Leonardo | AAA1111 |
| Foo      | Audi    |
| Foo      | 2008    |
| Foo      | 2008    |
| Foo      | XXX1234 |
| Bar      | Belina  |
| Bar      | 1970    |
| Bar      | 1971    |
| Bar      | XXX1234 |
+----------+---------+
(16 registros)

Bloguei!