Hi, i didn't know
where to send this, so forgive me if this isn't the right
list.
In our company we
had been using PostgreSQL for development for more than 2 years now, it's an
excelent RDBMS, the only thing we miss is the ability to call
stored procedures (functions returning records in PG) with a simplier
syntax, but at last we get used to... one problem that we all know is the
compiled pl/pgsql script that "remembers" the OID of temporary tables, and the
alternative that PG devs recommend is using EXECUTE() to execute "dinamically"
the query inside the function, this is tedious because for queries that uses the
parameters of the function you have to concatenate them, and sometimes you can
spend more time in debugging concatenation issues than the query
itself.
So, since
the PREPARE/EXECUTE (would be a little easier to code if supported) don't
work in pl/pgsql and the OIDs are remembered, we develop a function that
recompiles another function, i attach it here for your review.
This way before
calling a function with temporary tables we call "select
recompilar('function_name')" and then call the actual function, that once
recompiled works well. This is equivalent to recreate the function from a
command line.
We know this is not
the right way to do it and it affects the performance since PG must recompile
the function script, but for functions that aren't called frecuently and are
dificult to code using concatenatios this is a good
approach.
This version only
works on PostgreSQL 8 since it looks for argument names in the declaration of
the function, you can remove the references to proargnames and should work on
7.x too.
Best
regards.
--------------------------------------------------
Lic. Maximiliano Di Rienzo
IT
Manager
M.P.: 2502 - MCP ID:
2725911
Tel.: 351
4283419 - Cel.: 351 155901603
Fulfill
Technology S.A.
27 de
Abril 424 - Piso 3 Of. A - Córdoba
--------------------------------------------------------------------------------------------
Atención: Este mail es confidencial. En el caso que usted no sea el destinatario, no esta autorizado a reproducir o divulgar a terceros el contenido de este mensaje. Si usted lo ha recibido por error, por favor informenos inmediatamente devolviendo el correo electrónico y borrando el documento.
Attention: This E-Mail is confidential. If you are not the intended recipient, you must not copy, disclose or use its contents. If you have received it in error, please inform us immediately by return E-Mail and delete the document.
Atenção: Esta mensagem, e qualquer de seus anexos, eh confidencial e privilegiada. Caso voce nao seja o destinatario, nao esta autorizado a reproduzir ou divulgar a terceiros o conteudo desta mensagem e de qualquer anexo da mesma e deve apagar com os seus respectivos anexos.
Aufmerksamkeit: Dieses E-Mail ist vertraulich. Wenn Sie nicht der rechtmaessige Empfaenger sind, duerfen Sie den Inhalt weder kopieren, verbreiten oder benutzen. Sollten Sie dieses E-Mail versehentlich erhalten haben, senden Sie es bitte an uns zurueck und loeschen es anschliessend.
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
Atención: Este mail es confidencial. En el caso que usted no sea el destinatario, no esta autorizado a reproducir o divulgar a terceros el contenido de este mensaje. Si usted lo ha recibido por error, por favor informenos inmediatamente devolviendo el correo electrónico y borrando el documento.
Attention: This E-Mail is confidential. If you are not the intended recipient, you must not copy, disclose or use its contents. If you have received it in error, please inform us immediately by return E-Mail and delete the document.
Atenção: Esta mensagem, e qualquer de seus anexos, eh confidencial e privilegiada. Caso voce nao seja o destinatario, nao esta autorizado a reproduzir ou divulgar a terceiros o conteudo desta mensagem e de qualquer anexo da mesma e deve apagar com os seus respectivos anexos.
Aufmerksamkeit: Dieses E-Mail ist vertraulich. Wenn Sie nicht der rechtmaessige Empfaenger sind, duerfen Sie den Inhalt weder kopieren, verbreiten oder benutzen. Sollten Sie dieses E-Mail versehentlich erhalten haben, senden Sie es bitte an uns zurueck und loeschen es anschliessend.
--------------------------------------------------------------------------------------------
Recompilar.sql
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
