current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I need to create a function that compute account sum for each branch and compare to branch balance.

I tried to do this:

CREATE OR REPLACE FUNCTION t3() RETURNS TABLE(bb integer, sum integer) AS $$ 
BEGIN
FOR i IN 1..50 LOOP
BEGIN
SELECT bbalance FROM branch WHERE branchid = i
union
SELECT  sum(balance) FROM account WHERE branch = i;
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;

I'm kinda confused right now... I would like it to return a table with 2 columns: one with the value of bbalance from the branch; the second one with the sum of every balance in that branch.

I need something that I can call with select * from t3().

That's what I write in pgAdmin query editor:

CREATE OR REPLACE FUNCTION t3() RETURNS TABLE(bb integer, sum integer) AS $$ 
DECLARE
   bal integer;
BEGIN
  FOR i IN SELECT branchid FROM branch
  LOOP
    SELECT INTO bb bbalance FROM branch WHERE branchid = i;
    SELECT INTO "sum" sum(balance) FROM account WHERE branch = i;
    RETURN NEXT;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

And the error is:

ERRORE:  variabile del ciclo sulle righe deve essere una variabile di tipo row o record o una lista di variabili scalari
LINE 5:   FOR i IN SELECT branchid FROM branch
              ^

********** Error **********

ERRORE: variabile del ciclo sulle righe deve essere una variabile di tipo row o record o una lista di variabili scalari
SQL state: 42601
Character: 119

Italian - translation is:
loop variable of loop over rows must be a record or row variable or list of scalar variables.

share|improve this question
    
Run SET lc_messages = 'C'; in your session to get English error messages. –  Erwin Brandstetter Jul 30 at 0:18

1 Answer 1

up vote 3 down vote accepted

You need to set the table variables than RETURN NEXT, e.g.

CREATE OR REPLACE FUNCTION t3() RETURNS TABLE(bb integer, sum integer) AS $$ 
DECLARE
   i integer;
BEGIN
  FOR i IN SELECT branchid FROM branch
  LOOP
    SELECT INTO bb bbalance FROM branch WHERE branchid = i;
    SELECT INTO "sum" sum(balance) FROM account WHERE branch = i;
    RETURN NEXT;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

but really, all this can be done much more cleanly without PL/PgSQL at all as a trivial GROUP BY over a join:

SELECT b.branchid, b.bbalance, coalesce(sum(a.balance), 0) AS acctbalance
FROM branch b LEFT OUTER JOIN account a ON (b.branchid = a.branch)
GROUP BY b.branchid;

BTW, "sum" is a poor choice for a parameter name given that it's a commonly used built-in aggregate function.

share|improve this answer
    
I tried and it threw this: error return next cannot have a parameter in function with out parameters –  Sanci Jul 29 at 22:22
    
@Sanci Fixed, see above. –  Craig Ringer Jul 29 at 22:23
    
Now it says: loop variable of loop over rows must be a record or row variable or list of scalar variables. You're right for parameters name, i'm writing quite randomly and basically names just to test it –  Sanci Jul 29 at 22:31
    
@Sanci Show the updated function and exact error please (as an edit to the question), comment here when done. –  Craig Ringer Jul 29 at 22:43
    
i edited the question @Craig –  Sanci Jul 29 at 22:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.