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