De qualquer forma, sugiro que faça o seguinte:
1) leia o capítulo "Server Programming" da documentação oficial: lá você aprenderá a criar as funções (não mais procedimentos!) em linguagem PL/PgSQL (tem até um tópico sobre conversão a partir da PL/SQL do Oracle)
2) converta primeiro as instruções de SELECT individualmente: você verá que algumas funções, como to_date() ou extract() talvez tenham que ser alteradas
3) faça uma função bem mais simplificada, e depois tente usar cursores (FOR..LOOP)
4) converta a stored procedure citada!
2006/1/23, Joªo Borges Claudino Junior <[EMAIL PROTECTED]>:
pessoal to me batendo para procedure do oracle para postgres, assim mando uma delas(a menor) para ver se algume abre minha cabeça ja li oq o manual diz a respeito porem não tem tudo oq preciso, lá vai:
AS
-----------------------------------------------------------------------------------------------------
-- PROCEDURE RETORNA DADOS SINGLERESULT DE 1HORA COM TRES TAGS
-----------------------------------------------------------------------------------------------------
PROCEDURE edc_singleresult15m
(myref IN OUT ref_cursor, ntag1 IN char, ntag2 IN char, ntag3 IN char , dt1 char , dt2 char,itpFunc integer)
IS
r1 edc_sql_2tag_grp.ref1;
vtable varchar(30);
mySql varchar(32767);
ipos int;
iAVG varchar(100);
BEGIN
-- LEITURA DOS NOMES DAS TABELAS DISPONIVEIS NA TABELA DE CONTROLE --
ipos := 0;
if (itpFunc = 1) then iAVG := ' avg(valor) as Consumo '; end if;
if (itpFunc = 2) then iAVG := ' max(valor) as Consumo '; end if;
if (itpFunc = 3) then iAVG := ' min(valor) as Consumo '; end if;
mySql := 'SELECT BH_NOME FROM BH_PAS_H_CTL ' ||
' where BH_DTHR >= to_date( ''' || dt1 || ''',''dd-mm-yyyy'') and BH_DTHR <= to_date( ''' || dt2 || ''',''dd-mm-yyyy'')';
OPEN r1 FOR mySql ;
LOOP
FETCH r1 INTO vtable;
EXIT WHEN r1%NOTFOUND;
if( ipos = 0) then
mySql := 'SELECT ' ||
' Id,nome,' ||
' extract(day from cast(BH_DTHR as timestamp with local time zone)) as Dia,' ||
' extract(month from cast(BH_DTHR as timestamp with local time zone)) as Mes, ' ||
' extract(year from cast(BH_DTHR as timestamp with local time zone)) as Ano, ' ||
' extract(hour from cast(BH_DTHR as timestamp with local time zone)) as Hora, ' ||
' extract(minute from cast(BH_DTHR as timestamp with local time zone)) as Minuto, ' ||
' extract(second from cast(BH_DTHR as timestamp with local time zone)) as Segundo,' ||
iAVG ||
' FROM pas_r,' || vtable ||
' where ' || vtable || '.bh_chave = pas_r.bh_chave and pas_r.id = ''' || ntag1 || ''' ' ||
' or pas_r.id = ''' || ntag2 || ''' ' || ' or pas_r.id = ''' || ntag3 || ''' ' ||
' and BH_DTHR >= to_date( '''|| dt1 ||''' ,''dd-mm-yyyy'') and BH_DTHR <= to_date( '''|| dt2 ||''',''dd-mm-yyyy'')' ||
' group by id,nome, extract(second from cast(BH_DTHR as timestamp with local time zone)), '||
' extract(minute from cast(BH_DTHR as timestamp with local time zone)), ' ||
' extract(hour from cast(BH_DTHR as timestamp with local time zone)) , ' ||
' extract(day from cast(BH_DTHR as timestamp with local time zone)), ' ||
' extract(month from cast(BH_DTHR as timestamp with local time zone)), ' ||
' extract(year from cast(BH_DTHR as timestamp with local time zone)) ' ;
else
mySql := mySql || ' UNION SELECT ' ||
' Id,nome,' ||
' extract(day from cast(BH_DTHR as timestamp with local time zone)) as Dia, ' ||
' extract(month from cast(BH_DTHR as timestamp with local time zone)) as Mes, ' ||
' extract(year from cast(BH_DTHR as timestamp with local time zone)) as Ano, ' ||
' extract(hour from cast(BH_DTHR as timestamp with local time zone)) as Hora, ' ||
' extract(minute from cast(BH_DTHR as timestamp with local time zone)) as Minuto, ' ||
' extract(second from cast(BH_DTHR as timestamp with local time zone)) as Segundo, ' ||
iAVG ||
' FROM pas_r,' || vtable;
mySql := mySql || ' where ' || vtable || '.bh_chave = pas_r.bh_chave and pas_r.id = ''' || ntag1 || ''' ' ||
' or pas_r.id = ''' || ntag2 || ''' ' || ' or pas_r.id = ''' || ntag3 || ''' ' ||
' and BH_DTHR >= to_date( '''|| dt1 ||''' ,''dd-mm-yyyy'') and BH_DTHR <= to_date( '''|| dt2 ||''',''dd-mm-yyyy'')' ||
' group by id,nome, extract(second from cast(BH_DTHR as timestamp with local time zone)), '||
' extract(minute from cast(BH_DTHR as timestamp with local time zone)), ' ||
' extract(hour from cast(BH_DTHR as timestamp with local time zone)) , ' ||
' extract(day from cast(BH_DTHR as timestamp with local time zone)), ' ||
' extract(month from cast(BH_DTHR as timestamp with local time zone)), ' ||
' extract(year from cast(BH_DTHR as timestamp with local time zone)) ' ;
end if;
ipos := ipos + 1;
END LOOP;
mySql := mySql || ' order by Dia,Mes,Ano,Hora, Minuto, Segundo ';
OPEN myref FOR mySql ;
-- FIM DA CRIACAO DO CURSOR DA LEITURA DE TODAS AS TABELAS DISPONIVEIS --
END edc_singleresult15m;
_______________________________________________
Grupo de Usuários do PostgreSQL no Brasil
http://www.postgresql.org.br
--
Atenciosamente,
Rodrigo Hjort
GTI - Projeto PostgreSQL
CELEPAR - Cia de Informática do Paraná
http://www.pr.gov.br
_______________________________________________ Grupo de Usuários do PostgreSQL no Brasil http://www.postgresql.org.br
