Function with loop not returning values

Question

I am trying to write a loop that returns max(lead) for each opp. Right now, I am just returning a row of nulls. What am I doing wrong?

This is my table:

CREATE TEMP TABLE mytable (
  opp  text
, pty_id text 
, lead int);

INSERT INTO mytable VALUES
('A', '01', 1)
,('A', '01', 2)
,('A', '01', 3)
,('B', '01', 1)
,('B', '01', 2)
,('B', '01', 3)
,('C', '02', 4)
,('C', '02', 5)
,('D', '01', 1)
,('D', '01', 2)
,('D', '01', 3);

Here is the function:

DROP FUNCTION grp_prod();
CREATE OR REPLACE FUNCTION grp_prod()
  RETURNS TABLE (
    opp text
  , pty_id text
  , lead int
  , result int) AS
$BODY$
DECLARE
    r mytable%ROWTYPE;
BEGIN
    opp    := $1; 
    pty_id  := $2;  
    lead := $3;
    result  := null;
FOR r IN
    SELECT *
    FROM mytable m
    ORDER BY m.opp, m.lead
LOOP
    IF (r.opp, r.lead) <> (opp, lead) THEN 
        RETURN NEXT;
        opp    := r.opp;
        lead   := r.lead;
        result  := 1;
    END IF;

    result := MAX(lead);
END LOOP;
RETURN NEXT; 
END;
$BODY$ LANGUAGE plpgsql STABLE;

select * from grp_prod();

I really appreciate any help you can provide here. Please let me know if you have any further questions if my requirements are unclear.

8.2.15... I think one problem is this pointer is not working — user3930968, Aug 12 '14 at 00:25
I think your first problem is the [hopelessly outdated version](http://www.postgresql.org/support/versioning/). Upgrade to a current version if at all possible. If impossible, at least upgrade to the latest point release 8.2.23. — Erwin Brandstetter, Aug 12 '14 at 01:55

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

Your function is broken in too many places.
Looks like you can just use this simple query instead:

SELECT DISTINCT ON (opp)
       *
FROM   mytable
ORDER  BY opp, lead DESC;

Should even work in Postgres 8.2.
But this is just an educated guess, your question is inconclusive.

Details for DISTINCT ON:

Select first row in each GROUP BY group?

edited May 23 '17 at 12:05

Community

1
1

answered Aug 12 '14 at 02:11

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 2 · answered Aug 12 '14 at 02:40

0

If you really want a function to run this, this will work for you:

CREATE or replace FUNCTION test.grp_prod()
    RETURNS SETOF mytable AS
$BODY$
BEGIN

    RETURN QUERY SELECT DISTINCT ON (opp) opp,pty_id,MAX(lead) OVER (PARTITION BY opp) FROM mytable;
END
$BODY$
    LANGUAGE plpgsql STABLE;

SELECT * FROM grp_prod()

However, it seems unnecessary when you can simply run a query to get the result you desire.

Result: Max lead of each opp

answered Aug 12 '14 at 02:40

NullEverything

450
2
5

Sorry, the max(lead) was just a placeholder for more complex criteria that I will insert after. I was just trying to get the structure of the loop to work. Thanks! – user3930968 Aug 12 '14 at 13:35

Function with loop not returning values

2 Answers2