Re: nested SQL with SPI

From: Markus Wagner <wagner(at)imsd(dot)uni-mainz(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: nested SQL with SPI
Date: 2001-09-04 07:23:04
Message-ID: 01090409230400.04283@magnus
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hartwig Jens 2001-09-04 07:25:56 Book "PostgreSQL" to be released in November 2001
Previous Message Giorgio Volpe 2001-09-04 07:00:55 (no subject)