I have to build one function which will delete all the record in the table which satisfies specified condition except latest 20 records .
for this i have writen one query which seems logically good but not working on postgreSQL, because postgreSQL doesn't support order by and limit inside subquery.
Please tell me any other way to achieve this task, since its very important for me.
Thanks for quick response
My Query is :
create function funNotificationLimit() returns opaque
as
'declare serverId int;
begin
serverId=(select NEW.srvserverid);
delete from reporttable where srvserverid=serverId and
rptreportid not in(select rptreportid from reporttable
where srvserverid=serverId order by rpttimestamp desc limit 20);
return NEW;
end;'
language 'plpgsql';
for this i have writen one query which seems logically good but not working on postgreSQL, because postgreSQL doesn't support order by and limit inside subquery.
Please tell me any other way to achieve this task, since its very important for me.
Thanks for quick response
My Query is :
create function funNotificationLimit() returns opaque
as
'declare serverId int;
begin
serverId=(select NEW.srvserverid);
delete from reporttable where srvserverid=serverId and
rptreportid not in(select rptreportid from reporttable
where srvserverid=serverId order by rpttimestamp desc limit 20);
return NEW;
end;'
language 'plpgsql';