Обсуждение: What is PG best practice for storing temporary data in functions?

Поиск
Список
Период
Сортировка

What is PG best practice for storing temporary data in functions?

От
"Ben"
Дата:
<div class="Section1"><p class="MsoNormal"><span lang="EN-GB" style="FONT-SIZE: 12pt">There is a very common technique
usedin <span class="947012712-05072004">other RDBMS (e.g. </span>Sybase<span class="947012712-05072004">)</span> stored
procedureprogramming which I am not sure how best to replicate in Postgres functions.</span><p class="MsoNormal"><span
lang="EN-GB"style="FONT-SIZE: 12pt"> </span><p class="MsoNormal"><span lang="EN-GB" style="FONT-SIZE: 12pt">A Sybase
storedprocedure can do</span><p class="MsoNormal"><span lang="EN-GB" style="FONT-SIZE: 12pt"><span style="mso-spacerun:
yes"> </span><span class="GramE">select</span> <some complex query> into #temp1</span><p class="MsoNormal"><span
lang="EN-GB"style="FONT-SIZE: 12pt"><span style="mso-spacerun: yes">  </span><span class="GramE">create</span> table
#temp2(…)</span><p class="MsoNormal"><span lang="EN-GB" style="FONT-SIZE: 12pt"><span style="mso-spacerun: yes"> 
</span><spanclass="GramE">call</span> some proc which also uses #temp1 and #temp2</span><p class="MsoNormal"><span
class="GramE"><spanlang="EN-GB" style="FONT-SIZE: 12pt">etc</span></span><span lang="EN-GB"></span><p
class="MsoNormal"><spanlang="EN-GB" style="FONT-SIZE: 12pt"> </span><p class="MsoNormal"><span class="GramE"><span
lang="EN-GB"style="FONT-SIZE: 12pt">where</span></span><span lang="EN-GB"> #temp1 and #temp2 are temporary tables
magicallycreated by the server for the duration of the procedure call only (the procedure can be safely executed in
parallelsince each execution sees only its own data). Under the hood, it does this by mangling the names of the temp
tableswith a unique identifier for the procedure context.</span><p class="MsoNormal"><span lang="EN-GB"
style="FONT-SIZE:12pt"> </span><p class="MsoNormal"><span lang="EN-GB" style="FONT-SIZE: 12pt">What are the cleanest
andmost performant ways to do this in Postgres (7.4.2)? <span class="947012712-05072004">I am aware of temporary tables
butthey are globally visible to other invocations in the same session. We need the equivalent of local method variables
Iguess.</span></span><p class="MsoNormal"><span lang="EN-GB" style="FONT-SIZE: 12pt"> </span><p class="MsoNormal"><span
lang="EN-GB"style="FONT-SIZE: 12pt"> </span></div>