Arquivo

Archive for the ‘Procedimentos armazenados’ Category

plperl uncrosstab (descruzamento de tabelas)

fevereiro 20, 2008 Deixe um comentário

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!

Anúncios