'Postgres - Return query in function is too slow
I am having a postgres(9.6) function where I am manipulating data in a table. the code is written in block and I want to return data after end of data manipulation part. Here is my function code look like,
CREATE OR REPLACE FUNCTION myfunction(file_id bigint)
RETURNS SETOF record AS
$BODY$
DECLARE
BEGIN
raise info 'start %',clock_timestamp();
-- code starts here
-- many update on mytable in a cursor loop;
-- many insert happens to mytable;
-- code ends here
raise info 'end%',clock_timestamp();
RETURN QUERY execute select * from mytable;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
This works well and the difference between start and end time is less than a second. But when I execute function it takes >30 seconds to show the data in pgadmin. As per the requirement the data should be fetched after the function execution. Please suggest what could be wrongdoing and how to improve the function performance. Thanks
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
