Обсуждение: Re: Calling a stored procedure from another stored procedure...

Поиск
Список
Период
Сортировка

Re: Calling a stored procedure from another stored procedure...

От
Ezequiel Tolnay
Дата:
Christophe Geers wrote:
> I came as far as getting the first returned result by performing a 
> SELECT INTO mytype function calculate_cost(...)...etc., which is normal 
> I guess since a SELECT INTO only returns a single row according to the 
> manual. However is there a way to loop / iterate all of the results?

SELECT INTO a variable will only get the first row of the returning 
rowset. To get the full rowset into a temporary table, you'll have to 
use INSERT INTO MYTEMPTABLE SELECT * FROM MYFUNCTION(), but working with 
temporary tables is never very pleasing in postgresql.

I recommmend you to iterate over the results by doing the following:

_t := 0;
FOR _myrecord IN SELECT * FROM myfunction(...)
LOOP  _t := _t + _myrecord.mycolumn;
END LOOP;

Cheers,

Ezequiel Tolnay