Обсуждение: pgplsql temporary tables
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.
--------------------------------------------------------------------------------------------
Вложения
>Hi, i didn't know where to send this, so forgive me if this isn't the right >list. probably general. > compiled pl/pgsql script that "remembers" the OID of temporary tables, Have you considered that if the temp table is created outside of the function that uses it, this is not a problem? Make one function, init_temp_tables() which is called after connection. It creates all your tables so they are persistent between calls. (FWIW, your function is clever). Merlin