CODEWITHSUNDEEP
postgresqlstored-functionstable-functions
abraham
abraham

Reputation: 37

How to retrieve multiple result rows in a Postgres functions?

Actually I just want to return more than one result rows by using the code below. But I have received only one row of the result set while calling the function (Date is not an issue because I have checked with the same date in a normal select query – it retrieves multiple result rows).

How to retrieve multiple result rows by using the below function?

CREATE OR REPLACE FUNCTION fun_audit_trail(in as_on_date date, out mail_id varchar, out user_id varchar, out user_name varchar, 
        out last_login_time timestamp, out last_logout_time timestamp, out logout_flag varchar, out user_available_flag varchar)
 AS 
$BODY$

BEGIN

    select am.am_usrmailid,am.am_usr_loginid,am.am_usr_name,am.am_last_login_time,am.am_last_logout_time,am.am_logout_flag,am.am_usr_available_flag 
    into mail_id,user_id,user_name,last_login_time,last_logout_time,logout_flag,user_available_flag
    from auth_monitor am where am_last_login_time <= as_on_date;

END $BODY$
  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

Upvotes: 1

Views: 54

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246473

All you have to do is add

RETURNS SETOF record

after the parameter list.

Upvotes: 1

Related Questions