Postgres cursor

Question

I created Composite Type

Create Type TestDetailReportType1 As
(
sName text,
cDetailsTimeStamp  timestamp,
number text,
dropdi text,
queue text,
agent text,
status int,
reference int
)

I created a cursor which i expect to return a list of composite type ...but no records is returned when i execute select * from TestdetailsCursortest11("abc") but the query written within the function when executed directly returns 31 row...i am new to postgress so i fail to understand which place i am going wrong while making this function ,really appreciate any guidance at the front.

Note->I specifically want to write a cursor in this scenario...I was successfully able to get result when the function was returning table.

CREATE OR REPLACE FUNCTION public.TestdetailsCursortest11(
    hgname text)
    RETURNS SETOF TestDetailReportType1
    LANGUAGE 'plpgsql'
AS $TestdetailsCursortest11$

DECLARE
   cDetailcursor refcursor;
   cDetailtEvent  RECORD;     -- variable to store agent event.
   cDetail callDetailReportType1;
BEGIN
    OPEN cDetailcursor FOR
    select tblUsers.UserName,tblCallEvent.StateCreateDate,tblCallRegister.Cli,tblCallRegister.DDI,tblhuntGroup.name,
    tblUsers.Extension,
    tblCallEvent.StateID,
    tblCallRegister.CallID
  from tblCallRegister
    inner join tblCallEvent on tblCallRegister.callregisterid= tblCallEvent.callregisterid 
    inner join tblUsers on tblUsers.userid=tblCallEvent.agentid 
    inner join tblhuntGroup on tblhuntGroup.HGID=tblCallEvent.HGID 
  where name=hgname;
FETCH NEXT FROM callDetailcursor INTO callDetailtEvent;
  callDetail.sName=callDetailtEvent.UserName;
  callDetail.cDetailsTimeStamp=callDetailtEvent.StateCreateDate;
  callDetail.number =callDetailtEvent.Cli;
IF callDetailtEvent.StateID = 19
THEN
  callDetail.dropdi=callDetailtEvent.DDI;
ELSE
  callDetail.dropdi=callDetailtEvent.DDI+1;
END IF;
  callDetail.queue=callDetailtEvent.name;
  callDetail.agent=callDetailtEvent.Extension;
  callDetail.status =callDetailtEvent.StateID;
  callDetail.reference=callDetailtEvent.CallID;
RETURN;
CLOSE callDetailcursor;

END;
$TestdetailsCursortest11$;

Laurenz Albe · Accepted Answer · 2016-11-13 11:39:32Z

1

In a set returning function (a.k.a. table function) you use RETURN not to return a result, but to exit the function.

You use RETURN NEXT <value>; to return a result row. So your function should look similar to this:

DECLARE
   cDetail callDetailReportType1;
   cDetailtEvent RECORD;
BEGIN
   FOR cDetailtEvent IN
      SELECT ...
   LOOP
      cDetail.field1 := ...;
      cDetail.field2 := ...;

      /* return the next result row */
      RETURN NEXT cDetail;
   END LOOP;

   /*
    * This is optional; dropping out from the end
    * of a function is an implicit RETURN
    */
   RETURN;
END;

The way your function is written it will alwazs return an empty result because there is no RETURN NEXT <value>;.

answered Nov 13, 2016 at 11:39

Laurenz Albe

257k22 gold badges311 silver badges387 bronze badges

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

2 Comments

Aashish Mukherjee Over a year ago

Thanks Laurenz Albe ....Your Suggestion help me to see the Result... Just one point here i was expecting 31 rows but only 1 row the first row of the query is only getting returned ...could you help me here ..I have not added loop just the cursor

Laurenz Albe Over a year ago

Well, without the loop the RETURN NEXT will only be executed once, so it is no surprise if you get only one result row. The PostgreSQL documentation has a couple of examples of how to write loops in PL/pgSQL, and the simplest way is the code I posted in my answer, which uses an implicit cursor rather than an explicit one.

Collectives™ on Stack Overflow

Postgres cursor

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related