f



Calling a stored procedure from another stored procedure...

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
0
Christophe
5/4/2005 8:24:12 AM
comp.databases.postgresql.sql 1301 articles. 0 followers. Post Follow

1 Replies
676 Views

Similar Articles

[PageSpeed] 19

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

0
Christophe
5/9/2005 7:41:43 AM
Reply: