Dear Helen,

I'm in no position to argue with the experts here, but wouldn't it be
simplier just to use

for select retval1, retval2, ...
from storedproc(params)
into :triggervar1, :triggervar2, ...
do
begin
<process a single record returned by storedproc>
end

Besides, the while loop you hinted executes the SP independently in
every iteration, so it'll keep getting the first record from the SP (OK,
it also depends on the procedure itself, and the arguments passed to it
in a particular iteration; probably you can make a procedure in such a
way that it can be persuaded to return only a specified record from its
result set, but what's the point?).
So, unless I got it totally wrong, a SP returning multiple records can
be used with suspend inside and for select... in the caller, and a SP
returning always a single vector of values (a single record, no point in
suspend here) doesn't require a loop in the caller at all, just a simple
execute procedure statement.
Could you please clarify a bit how's your loop supposed to work? I've
been using "for select..." from stored procedures for ages, but if
there's a better (more efficient, elegant, whatever) way of doing this,
I'd love to learn.
Well, if I _did_ get it totally wrong, please just ignore this letter.

regards
Tomasz


W dniu 2011-11-28 20:32, Helen Borrie pisze:

> At 02:25 AM 29/11/2011, Thamiris Monteiro wrote:
>> hi there!
>>
>> I'm new at PSQL and i'm having some issues, so i'd appreciate any help or
>> advice...
>>
>> here's the deal: i made a stored procedure that selects a huge amount of
>> info from a lot of tables and uses "suspend". it works, the result set
>> shows columns with all the infos i wanted.
>> My real aim is to have a trigger that calls this SP when anything happens
>> at a certain table, and i'm having some problems with that.
>>
>> How do i call a SP from inside a trigger? I tried "select * from
>> <storedprocedure>", but it doesn't work...
>
> Remove SUSPEND from the SP.
>
> Then, call the procedure like this if the trigger wants only a one-row set:
> execute procedure proc1 (arg1, arg2, .....)
> returning_values (:var1, :var2, ....)
> /* then do what you want with the vars */
>
> PSQL can't do anything with a multi-row set except step through it, row by row, in a loop. If you want the trigger to use values from a set of multiple rows, determine an end condition and put the EXECUTE PROCEDURE in a WHILE loop controlled by variable:
> ...
> declare variable looper smallint=0;
> ....
> begin
> ....
> /* initialise your args and vars here */
> while (looper = 0) do
> begin
> /* assign values to your args */
> execute procedure proc1 (arg1, arg2, .....)
> returning_values (:var1, :var2, ....)
> if (<end condition>) then
> begin
> looper = 1;
> leave;
> end
> /* do what you want with the vars */
> /* re-initialise the vars */
> end
> ....
> end
> ....
> end
>
> Don't take this as an absolute template for what you want to do, it's just to give you the idea for designing the flow that you need.
>
> ./heLen
>
>
>
>
>
>
>

--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__