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

Conversões implícitas no PostgreSQL (8.3)

fevereiro 14, 2008 1 comentário

Vejam bem, o assunto é polêmico, inconclusivo, recorrente… mas ainda assim vou dar continuidade.

Na versão 8.3 algumas conversões implícitas para o tipo texto foram retiradas à favor de avaliação de expressões envolvendo este tipo um pouco mais confiáveis. Então as expressões:

postgres=# \d foobar
Table “public.foobar”
Column | Type | Modifiers
——–+———+———–
a | integer |
b | text |

SELECT a=b FROM foo;

SELECT substring(9999,2);

[…]

… não devem mais funcionar.

Nosso problema é que a aplicação possuía muitos trechos de código utilizando este tipo de expressão. Ok, falha na aplicação, mas vai explicar isso pro cliente …

Os próximos parágrafos não pretendem de forma alguma discordar das decisões do PGDG em relação à retirada das conversões do núcleo do PostgreSQL e também não são aconselhados em hipótese alguma, salvo a minha situação que era emergencial 🙂 e decidi compartilhar. Estas medidas devem ser tomadas em caráter paliativo, enquanto a aplicação cliente não é corrigida.

Criando funções internas para as converções:

/* Baseado em $SRC/backend/utils/adt/int.c, int_text */

#include “postgres.h”
#include “fmgr.h”

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(int4_text);
Datum
int4_text(PG_FUNCTION_ARGS)
{
int32 arg = PG_GETARG_INT32(0);
text *result = (text *) palloc(12 + VARHDRSZ);
pg_ltoa(arg, VARDATA(result));

SET_VARSIZE(result,strlen(VARDATA(result)) + VARHDRSZ);
PG_RETURN_TEXT_P(result);
}

O código acima deverá gerar um objeto compartilhado e colocado em libdir (pg_config –libdir) do servidor.

Em seguida podemos criar o objeto para gerenciar as conversões:

CREATE OR REPLACE FUNCTION text(int4) RETURNS TEXT AS ‘int4_text’,’int4_text’ LANGUAGE ‘C’;

E depois a conversão propriamente dita:

CREATE CAST(int4 as text) WITH FUNCTION text(int4) AS IMPLICIT;

Testando:

SELECT 1=’1′::text;

?column?
———-
t
(1 row)

#regression: gmake installation check

Pronto, deve ser suficiente para este tipo de conversão funcionar.

Não esqueça que deve ser uma medida paleativa apenas. Existe muito mais por trás de uma conversão do que se pode imaginar. Talvez assunto para um próximo post.

Categorias:Núcleo

Sobre sequences e segurança multi-usuarios

fevereiro 13, 2008 Deixe um comentário

Existem alguns motivos para utilizar a função embutida nextval() ao inicializar uma sessão no postgres e querer-se acessar uma sequence.

Um dos motivos internos é necessidade de inicialização de itens compartilhados em SeqTableData. Embora possua um tipo semelhante com Relation uma sequence não poderia ser armazenada e recuperada no índice do descritor de cache de relações pois as entradas poderiam não mais estar disponíveis no próximo acesso.

A solução então é armazenar os valores necessários para se manipular as sequences em uma estrutura própria do tipo SeqDataTable. Dentre outros atributos este tipo armazena informações necessárias para validar o acesso, o oid e último valor conforme a descrição:

struct SeqTableData{

Oid relid,

bool last_valid,

int64 last,

[…]

} SeqTableData;

Uma vez que já possuímos o tipo, agora é necessário inicializar as informações com uma versão interna de nextval() conhecida neste ponto por nextval_internal(). A função de nextval_internal é receber o OID da sequence à que se deseja obter informação em pg_class e atribuir em SeqTable; associar páginas no buffer; inicializar operações de bloqueio (AccessShare) para permitir leituras concorrentes e, como já é conhecido, incrementar o valor de last com o valor do atributo increment_by desta sequence.

Categorias:Núcleo

Backspace do vim6.4

fevereiro 13, 2008 Deixe um comentário

Não entendo porque na versão 6.4  do vim a opção backspace está vazia desabilitando esta tecla para alterações fora de buffer.

Para resolver:

:se bs=start,eol

Categorias:Núcleo

Uma palavra sobre instruções dinâmicas plpgsql e plperl

fevereiro 8, 2008 Deixe um comentário

Problema recorrente e chato de se encarar é a necessidade de trabalhar com expressões dinâmicas na linguagem plpgsql.

De fato a linguagem plpgsql possue uma característica que permite que o plano de execução seja salvo na primeira chamada para uma instrução, desta forma dispensando a etapa de planejamento na próxima vez em que aquela instrução for executada dentro daquela mesma sessão e optimizando o acesso. Por este motivo, para situações regulares onde se é necessário criar instruções dinâmicas, o uso do comando EXECUTE dentro de funções é aconselhado, com o preço do não-salvamento dos planos de execução dos trechos de código onde este comando foi utilizado (senão, não era dinâmico ;-)).

Porém, pense na seguinte situação:

<code>

Tipo.atributo := OutroTipo.outroAtributo;

</code>

Se quisermos atribuir OutroTipo.outroAtributo de forma que “outroAtributo” for uma expressão gerada em tempo de execução, não conseguiremos utilizando plpgsql, pelo facto da variável OutroTipo fazer parte do contexto de variáveis do plpgsql e por este motivo não aceitando substituição com o comando EXECUTE.
Então, logo:

FOR i IN 1..10 LOOP

EXECUTE OutroTipo.array[i] INTO Tipo.atributo;

ou qualquer outro tipo de atribuição dinâmica não deverá funcionar, causando uma exceção não-recuperável.

Quando se tem necessidade específica para geração de comandos dinâmicos e expressões complexas, o jeito mesmo é partir para outras linguagens procedurais que manipulem as construções SQL como texto puro ao invés de objetos gravados em memória.

O seguinte trecho em plperl propõe uma solução para o problema em questão:

@columns = (chave,valor1,valor2…);

$row = spi_exec_query($sql);

foreach(@columns[1..$#columns]){
push @$TipoAtributo, { chave=> $row->{@columns[0]},valor1=> $row->{$_}}
}

Agora o hash TipoAtributo possue os valores que supostamente não se conseguia recuperar com plpgsql.