Calling a stored procedure from another stored procedure...

Calling a stored procedure from another stored procedure...

am 04.05.2005 10:24:12 von Christophe Geers

Goodmorning,

I'm quite new to PostgreSQL, started off with version 8.0 some time ago.
Recently I've begun working with stored procedures and now I've come
accross a little problem / issue I can't seem to figure out.

A quick overview of the situation:

I have one stored procedure with returns a SET OF a new (record) type
I've declared.

For example:

mytype
index INTEGER;
description VARCHAR;
cost FLOAT;

function calculate_cost(...): returns set of mytype;

This stored procedure works without a problem. I can just do a "select *
from calculate_cost(...)" and I can iterate the returned set of mytype.

Now I have created another stored procedure, let's call if function
test(...). I want this function to call the calculate_cost(...) stored
procedure, iterate the returned set and for instance return the total
cost (sum of mytype.cost).

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?

If it is possible to do this with PostgresSQL, I'd be really grateful if
someone posted a little code snippet on how to do this.

Thx.

Regards

Chris

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

am 09.05.2005 09:41:43 von Christophe Geers

Well, just to provide an answer to my own question in this, what appears
to be, a dead newsgroup.

Just for the sake of not letting this question float unanswered forever:

FOR mytype IN SELECT * FROM calculate_cost(...) LOOP
--Do some stuff here.
END LOOP

Jeez, it was pretty simple afterall.

Regards

Chris