Thread: nested SQL with SPI

Hi,

can I nest SQL statements with SPI within trigger functions?
How can I distinguish the results in SPI_tuptable?

Thanks,

Markus

Please clarify.

An SPI function is free to call SPI manager again...

What exactly you are attempting to do?

On Thu, 30 Aug 2001, Markus Wagner wrote:

> Hi,
>
> can I nest SQL statements with SPI within trigger functions?
> How can I distinguish the results in SPI_tuptable?
>
> Thanks,
>
> Markus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

Markus Wagner <wagner@imsd.uni-mainz.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

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@imsd.uni-mainz.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

Markus Wagner <wagner@imsd.uni-mainz.de> writes:
> this would mean that all result tables are kept in memory until the trigger
> function exits?

Until you do SPI_freetuptable or SPI_finish, yes, an SPI result sticks
around.

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

Just the pointer.  You may or may not need to hang onto SPI_processed,
SPI_lastoid, or SPI_result; a copy of SPI_tuptable might be enough for
your purposes.

If the docs don't seem clear enough to you, feel free to submit a doc
patch ...

            regards, tom lane

Markus Wagner wrote:
[Charset iso-8859-15 unsupported, filtering to ASCII...]
> Hi,
>
> can I nest SQL statements with SPI within trigger functions?
> How can I distinguish the results in SPI_tuptable?

    You  can. Look at how PL/pgSQL deals with result sets used in
    FOR loops. Probably you want to look at 7.1 code, because the
    7.2 code uses SPI cursor support for that already.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com