Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: Calling a stored procedure from another stored procedure...

Posted on 2005-05-04 10:24:12 by 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

Report this message

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

Posted on 2005-05-09 09:41:43 by 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

Report this message