From: | "Purushotham" <purushotham(at)savitr(dot)com> |
---|---|
To: | <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Am unable to return after update/insert execute, let me know is it the way to create or else any other way? |
Date: | 2018-11-14 14:08:07 |
Message-ID: | 000001d47c23$79f5d2c0$6de17840$@savitr.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-11-14 15:18:42 | Re: BUG #15506: Foreign data wrapper (postgres_fdw) unexpected behavior |
Previous Message | Paul van der Linden | 2018-11-14 10:15:38 | Difference in queryplan for array-contains vs unnest |