Am unable to return after update/insert execute, let me know is it the way to create or else any other way?

-- FUNCTION: fundq.funduseraccessalert(integer, integer, timestamp without
time zone, character varying)

-- DROP FUNCTION fundq.funduseraccessalert(integer, integer, timestamp
without time zone, character varying);

CREATE OR REPLACE FUNCTION fundq.funduseraccessalert(

currentuserid integer,

qfmid integer,

usercurrentdate timestamp without time zone,

userflag character varying)

RETURNS TABLE(qfimid integer, userid integer, statusid bit,
useraccessdate timestamp without time zone, lastupdateddate timestamp
without time zone, username character varying, useremail character varying,
userphone character varying, userstatus text)

LANGUAGE 'plpgsql'

COST 100

VOLATILE SECURITY DEFINER

ROWS 1000

AS $BODY$

DECLARE isActive integer; existuserdate timestamp without time zone;lastUser
integer;

BEGIN

isActive=(select count(*) from fund_user_alerts where qfim_id=qfmId and
status=b'1');

existuserdate=(select lastuser_on from fund_user_alerts where qfim_id=qfmId
and status=b'1' ORDER BY lastuser_on DESC LIMIT 1);

IF isActive > 0 THEN

IF userflag='Ok' THEN

INSERT INTO
fund_user_alerts(qfim_id,user_id,status,lastuser_on,updated_date)

values(qfmId,currentuserId,b'1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP );

RETURN QUERY

select qfim_id as
QfimId,user_id as UserId,fa.status as StatusId,lastuser_on as
UseraccessDate,updated_date as lastupdatedDate,

p.first_name as UserName,
p.email as UserEmail, p.phone as UserPhone, 'true'::text as UserStatus

from fund_user_alerts fa inner join
person p on p.id = fa.user_id

where qfim_id=qfimId and
fa.user_id=currentuserid and fa.status=b'1';

ELSE IF userflag='Submit' THEN

UPDATE fund_user_alerts SET
lastuser_on=(now() + interval '1' hour),updated_date=CURRENT_TIMESTAMP

WHERE qfim_id=qfmId and
user_id=currentuserid and status=b'1';

RETURN QUERY

select qfim_id as
QfimId,user_id as UserId,fa.status as StatusId,lastuser_on as
UseraccessDate,updated_date as lastupdatedDate,

p.first_name as UserName,
p.email as UserEmail, p.phone as UserPhone,'true'::text as UserStatus

from fund_user_alerts fa inner join
person p on p.id = fa.user_id

where qfim_id=qfimId and
fa.user_id=currentuserid and fa.status=b'1';

ELSE IF userflag='Fund' THEN

IF ((now() - interval '1'
hour) < (existuserdate) ) THEN

RETURN QUERY

select qfim_id as
QfimId,user_id as UserId,fa.status as StatusId,lastuser_on as
UseraccessDate,updated_date as lastupdatedDate,

p.first_name
as UserName, p.email as UserEmail, p.phone as UserPhone,

(CASE WHEN
(currentuserid=user_id) then

'true'::text
else 'false'::text end) as UserStatus

from fund_user_alerts
fa inner join person p on p.id = fa.user_id

where
qfim_id=qfmId and fa.status=b'1';

ELSE

UPDATE fund_user_alerts
SET updated_date=CURRENT_TIMESTAMP, status=b'0'

WHERE qfim_id=qfmId and
status=b'1';

INSERT INTO
fund_user_alerts(qfim_id,user_id,status,lastuser_on,updated_date)

values(qfmId,currentuserId,b'1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP );

RETURN QUERY

select
qfim_id as QfimId,user_id as UserId,fa.status as StatusId,lastuser_on as
UseraccessDate,updated_date as lastupdatedDate,

p.first_name
as UserName, p.email as UserEmail, p.phone as UserPhone, 'true'::text as
UserStatus

from fund_user_alerts
fa inner join person p on p.id = fa.user_id

where
qfim_id=qfimId and fa.status=b'1';

END IF;

ELSE IF userflag='LogOFF' THEN

UPDATE
fund_user_alerts SET updated_date=CURRENT_TIMESTAMP, status=b'0'

WHERE
user_id=currentuserid and status=b'1';

select qfim_id as
QfimId,user_id as UserId,fa.status as StatusId,lastuser_on as
UseraccessDate,updated_date as lastupdatedDate,

p.first_name
as UserName, p.email as UserEmail, p.phone as UserPhone,'true'::text as
UserStatus

from fund_user_alerts
fa inner join person p on p.id = fa.user_id

where
user_id=currentuserid and fa.status=b'0';

ELSE

INSERT INTO
fund_user_alerts(qfim_id,user_id,status,lastuser_on,updated_date)

values(qfmId,currentuserId,b'1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP );

RETURN QUERY

select qfim_id as QfimId,user_id as
UserId,fa.status as StatusId,lastuser_on as UseraccessDate,updated_date as
lastupdatedDate,

p.first_name as UserName,
p.email as UserEmail, p.phone as UserPhone, 'true'::text as UserStatus

from fund_user_alerts fa inner join
person p on p.id = fa.user_id

where qfim_id=qfimId and
fa.status=b'1';

END IF;

END IF;

END IF;

END IF;

ELSE

INSERT INTO
fund_user_alerts(qfim_id,user_id,status,lastuser_on,updated_date)

values(qfmId,currentuserId,b'1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP );

RETURN QUERY

select qfim_id as QfimIdtemp,user_id as UserIdtemp,fa.status as
StatusIdtemp,lastuser_on as UseraccessDatetemp,

updated_date as lastupdatedDatetemp,p.first_name as
UserNametemp, p.email as UserEmailtemp, p.phone as UserPhonetemp,

'true'::text as UserStatustemp

from fund_user_alerts fa inner join person p on p.id = fa.user_id

where qfim_id=qfimId and fa.status=b'1';

END IF;

END;

$BODY$;

ALTER FUNCTION fundq.funduseraccessalert(integer, integer, timestamp without
time zone, character varying)

OWNER TO "GIPFundQ";

Best regards

PuruShotham Goud V

+91 9573 9573 54