Re: nested SQL with SPI

Hi Tom,

this would mean that all result tables are kept in memory until the trigger
function exits?

What do you mean with "copy it and associated globals into locals", just the
pointers or the data itself?

If I understand it the right way, I could something like in the code below,
e. g. loop through all classes, and within the loop, loop through all
corresponding attributes, and within make some statements, and whenever I
return from the attributes loop back to the classes loop, my results are
still valid?

Do you mean that after an arbitrary number of SPI_exec's the contents of the
corresponding SPI_tuptable pointers of all SPI_exec's before are still valid?

This would be cool!

Markus

----------

SPI_TupleTable * my_classes;
int num_my_classes;

SPI_exec ("SELECT * FROM pg_class WHERE ...",0);
my_classes = SPI_tuptable;
num_my_classes = SPI_processed;

for (i = 0;i < num_my_classes;i++)
{
char * my_value;
SPI_TupleTable * my_attributes;
int num_my_attributes;

my_value = SPI_getvalue (my_classes -> vals [i], my_classes -> tupdesc,...);

SPI_exec ("SELECT * FROM pg_attribute WHERE ...");
my_attributes = SPI_tuptable;
num_my_attributes = SPI_processed;

for (j = 0;j < num_my_attributes;j++)
{
SPI_exec ("SELECT ...,0);
...
}

}

On Monday 03 September 2001 21:16, you wrote:
> Markus Wagner <wagner(at)imsd(dot)uni-mainz(dot)de> writes:
> > can I nest SQL statements with SPI within trigger functions?
> > How can I distinguish the results in SPI_tuptable?
>
> Yes, but remember that SPI_tuptable is just a global variable. What
> you'll probably need to do is copy it and associated globals into locals
> of your function as soon as SPI_exec returns, and then use the local
> values to access that result. In this way, nested executions of
> SPI_exec won't confuse your access to your result.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster