Am unable to return after update/insert execute, let me know is it the way to create or else any other way? - Mailing list pgsql-bugs
| From | Purushotham |
|---|---|
| Subject | Am unable to return after update/insert execute, let me know is it the way to create or else any other way? |
| Date | |
| Msg-id | 000001d47c23$79f5d2c0$6de17840$@savitr.com Whole thread Raw |
| List | pgsql-bugs |
-- 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
pgsql-bugs by date: