Save data from table into a variable and use it inside a function (make a data set)

Question

Basically I want to make a data set like in PHP, where I can store the return of a select statement in a variable and then use it to do logical decisions.

here is what I am trying:

DROP FUNCTION cc_get_balance(date);

CREATE OR REPLACE FUNCTION cc_get_balance(theDate date) RETURNS TABLE(balance numeric(20,10), rate numeric(20,10), final_balance numeric(20,10)) AS $$

DEClARE
currency1_to_EUR numeric(20,10);
currency2_to_EUR numeric(20,10); 
table_ret record;
BEGIN

currency1_to_EUR := (SELECT rate FROM cc_getbalancesfordatewitheurs(theDate) WHERE from_currency = 'currency1' AND to_currency = 'EUR');
currency2_to_EUR := (SELECT rate FROM cc_getbalancesfordatewitheurs(theDate) WHERE from_currency = 'currency2' AND to_currency = 'EUR');

SELECT * INTO table_ret FROM cc_getbalancesfordatewitheurs(theDate);


END;                                                  

$$ LANGUAGE 'plpgsql';  

SELECT * FROM cc_get_balance('2014-02-15'::date);

I don't know if this is right. I want to be able to use table_ret as a data set like:

select * from table_ret ...

So I don't have to make a lot queries to the database. I have looked for examples doing this and have not found anything like what I need or want to do.

the version is 9.3.4, cc_getbalancesfordatewitheurs() returns a table with columns from_currency, to_currency, rate, exchange, balance and converted_amount. with around 30 rows. I need to run through the to_currency column and run some other conversions based on the currency list in the column. So I did not want to have to query the database 30 times for the conversions. All the data I need is collected together in the table returned by cc_getbalancesfordatewitheurs(). cc_get_balance() should return all the rows found in the table from the other function along with a column that does a final conversion of the to_currency into EUR

The essential question: are you talking about a single row / record or a **set** of rows? We would need to see what `cc_getbalancesfordatewitheurs()` returns. And your version of Postgres. Also, there is no indication in your code of what `cc_get_balance()` should actually return. — Erwin Brandstetter, Jul 02 '14 at 15:00
That's clarifying quite a bit. However, basic information should be [edited](http://stackoverflow.com/posts/24533707/edit) into the question. Comments are not the place for that. — Erwin Brandstetter, Jul 03 '14 at 00:14

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

Generally, there is not "table variable". You could use a cursor or a temporary table.
Better yet, use the implicit cursor of a FOR loop.
Even better, still, if possible, do it all in a single set-based operation. A query.

Related example:
Cursor based records in PostgreSQL

edited May 23 '17 at 11:57

Community

1
1

answered Jul 02 '14 at 19:05

Erwin Brandstetter

605,456
145
1,078
1,228

I'll add code, once you have edited your question with the missing details. – Erwin Brandstetter Jul 02 '14 at 19:05

Save data from table into a variable and use it inside a function (make a data set)

1 Answers1