PostgreSQL function returning a table

Question

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.

Run SET lc_messages = 'C'; in your session to get English error messages. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 30, 2014 at 0:18

Craig Ringer · Accepted Answer · 2014-07-29 22:51:40Z

3

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.

edited Jul 29, 2014 at 22:51

answered Jul 29, 2014 at 22:17

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Sanci91 Over a year ago

I tried and it threw this: error return next cannot have a parameter in function with out parameters

Craig Ringer Over a year ago

@Sanci Fixed, see above.

Sanci91 Over a year ago

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

Craig Ringer Over a year ago

@Sanci Show the updated function and exact error please (as an edit to the question), comment here when done.

Craig Ringer Over a year ago

@Sanci Oh, stupid editing error on my part, hold on. Declared the loop var as bal not i.

|

Collectives™ on Stack Overflow

PostgreSQL function returning a table

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related