Thread: table variables

Are there table variables in postgresql that you can include in a join?
If there are other suggestions as to how I can get done what I need
done, I would appreciate those as well.

What I am trying to do is write a multi step query, where it would be
much much much more efficient to create a temp table, so a single insert
and then populate it with update statements.
The problem is that you can't use a temp table in functions without
dynamic execute statements, and those don't work for other parts of the
code (besides the fact that they're confusing).

I was thinking a table variable, such as exists in msssql, would be the
perfect answer.

What I'll probably end up doing is having a permanent table and
including the ip address of the client machine, so that each time it is
used the data can be easily deleted.

On Thu, May 18, 2006 at 01:16:02PM +0200, Sim Zacks wrote:
> What I am trying to do is write a multi step query, where it would be
> much much much more efficient to create a temp table, so a single insert
> and then populate it with update statements.
> The problem is that you can't use a temp table in functions without
> dynamic execute statements, and those don't work for other parts of the
> code (besides the fact that they're confusing).

I'm thinking that we should just make temp tables work normally in
functions. Needs no new syntax and is what people expect anyway.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

I agree that the best situation would be to fix the temp tables so they
get replanned each time and therefore don't get converted to an oid.
However, now they are not fixed and I need my application to work. I was
hoping the table variables already existed, not as a request for an
additional feature.

Sim


Martijn van Oosterhout wrote:
> On Thu, May 18, 2006 at 01:16:02PM +0200, Sim Zacks wrote:
>> What I am trying to do is write a multi step query, where it would be
>> much much much more efficient to create a temp table, so a single insert
>> and then populate it with update statements.
>> The problem is that you can't use a temp table in functions without
>> dynamic execute statements, and those don't work for other parts of the
>> code (besides the fact that they're confusing).
>
> I'm thinking that we should just make temp tables work normally in
> functions. Needs no new syntax and is what people expect anyway.
>
> Have a nice day,