Dev Shed Forums - PostgreSQL Help http://forums.devshed.com/ PostgreSQL Help forum discussing administration, SQL syntax, or other PostgreSQL-related topics. PostgreSQL provides enterprise level database functionality at open source prices. en Sun, 25 Feb 2018 14:08:28 GMT vBulletin 60 http://forums.devshed.com/images/misc/rss.png Dev Shed Forums - PostgreSQL Help http://forums.devshed.com/ Changes in postgres config file for better performance of db http://forums.devshed.com/postgresql-help/979982-changes-postgres-config-file-performance-db-new-post.html Wed, 07 Feb 2018 05:15:05 GMT Hi there,

I need help to set postgres config file for my db. As I already made some changes in that
but it's taking time to response and consuming RAM more that I assigned for postgres. ]]>
PostgreSQL Help Amita Tyagi http://forums.devshed.com/postgresql-help-21/changes-postgres-config-file-performance-db-979982.html
Cursor format inside function http://forums.devshed.com/postgresql-help/979969-cursor-format-inside-function-new-post.html Mon, 05 Feb 2018 09:16:45 GMT Kindly look this function and edit the format because I have a doubt for it's format. All blocks are running individually but when I am executing this function it throws error that temp3 doesn't exist.

CREATE OR REPLACE FUNCTION dbo.usp_triplog_report(
user_id integer,
startdate timestamp without time zone,
enddate timestamp without time zone)
RETURNS TABLE(sys_service_id integer, vehicle_no character varying, reporting_location character varying, poi_id1 integer, check_in_time timestamp without time zone, start_latitude double precision, start_longitude double precision, check_out_time timestamp without time zone, turn_around_time text, destination character varying, poi_id2 integer, destination_checkin_time timestamp without time zone, end_latitude double precision, end_longitude double precision, destination_checkout_time timestamp without time zone, trip_duration text, total_distance double precision)
LANGUAGE 'plpgsql'

COST 100
VOLATILE
ROWS 1000
AS $BODY$

DECLARE
rows integer default 0;
v_sys_service_id INTEGER;
v_max INTEGER;
v_min INTEGER;
v_route_id INTEGER;

cur_serv_id cursor for select distinct(tm.sys_service_id) from trip_master tm where sys_user_id=$1;
cur_route_id cursor for select distinct(tm.route_id) from trip_master where sys_service_id=v_sys_service_id;
begin
drop table if exists temp,temp1,temp2,temp3;
GET DIAGNOSTICS rows=ROW_COUNT;
execute 'create temp table temp as
select id,sys_user_id,sys_service_id,poi_id,in_gps_time time1,out_gps_time time2,start_odometer,end_odometer from poi_log
where sys_user_id='||quote_literal($1)||' AND in_gps_time between '||quote_literal($2)||' and '||quote_literal($3)||'';

open cur_serv_id;
if rows>0
then
fetch next from cur_serv_id into v_sys_service_id;
while FOUND
loop
--truncate table temp1;
--truncate table temp2;
open cur_route_id;
if rows>0
then
fetch next from cur_route_id into v_route_id;
while Found
loop
v_min:=(select min(poi_id) from trip_route_detail where route_id=v_route_id);
v_max:=(select max(poi_id) from trip_route_detail where route_id=v_route_id);

execute 'create temp table temp1 as
SELECT p.id,p.sys_user_id,p.sys_service_id,td.route_id,td.stop_name as Reporting_location,
p.poi_id, p.time1,td.stop_latitude lat1,td.stop_longitude long1,p.time2,start_odometer,end_odometer,case when p.poi_id=v_min then 1
when p.poi_id=v_max then 0 else 5 end as stat,
ROW_NUMBER() OVER (ORDER BY p.sys_service_id,p.id) as rn
from temp p inner join trip_route_detail td
on p.poi_id=td.poi_id
where p.sys_service_id=v_sys_service_id and td.route_id=v_route_id and td.poi_id in (v_min,v_max)';

fetch next from cur_route_id into v_route_id;
end loop;
end if;
close cur_route_id;
deallocate cur_route_id;

execute 'create temp table temp2 as
select t.sys_user_id,t.sys_service_id,t.Reporting_location as start_location,t.poi_id,t.time1 as check_in_time,t.lat1 as start_latitude,t.long1 as start_longitude,
t.time2 as check_out_time, y.reporting_location as end_location, y.poi_id as poi_id2,y.time1 as check_in_time2,y.lat1 as end_latitude,y.long1 as end_longitude,
y.time2 as check_out_time2,(y.end_odometer-t.start_odometer) as total_distance from temp1 t left outer join
temp1 y
on t.rn+1=y.rn
where t.stat<>y.stat
and t.route_id=y.route_id
and t.stat in (0,1)';

execute 'create temp table temp3 as
select sys_service_id,s.veh_reg as vehicle_no,start_location as Reporting_location,poiid,check_in_time,
start_latitude,start_longitude,check_out_time,
CAST((DATE_PART(Minute,check_in_time-check_out_time)::INTEGER/60) AS TEXT) ||H||
CAST((DATE_PART(Minute,check_in_time-check_out_time)::INTEGER%60) AS TEXT) ||m as turn_around_time,
end_location as destination,poi_id2,check_in_time2 as destination_checkin_time,end_latitude,end_longitude,
check_out_time2 as destination_checkout_time,
CAST((DATE_PART(Minute,check_in_time-check_out_time)::INTEGER/60) AS TEXT) ||H||
CAST((DATE_PART(Minute,check_in_time-check_out_time)::INTEGER%60) AS TEXT) ||m as trip_duration,
total_distance from temp2 left join services s
on temp2.sys_service_id=s.id';

fetch next from cur_serv_id into v_sys_service_id;
end loop;
end if;
close cur_serv_id;

return query
select * from temp3;
end;

$BODY$;

ALTER FUNCTION dbo.usp_triplog_report(integer, timestamp without time zone, timestamp without time zone)
OWNER TO newtrack; ]]>
PostgreSQL Help Amita Tyagi http://forums.devshed.com/postgresql-help-21/cursor-format-inside-function-979969.html