The function is stuck just before the loop.
select * from scm_main.fn_connection_stations(1219646)
The message "Start..." is printed but message "... end" will not print.
CREATE OR REPLACE FUNCTION
scm_main.fn_connection_stations(var_connection_id bigint)
RETURNS SETOF scm_main.typ_connection_stations AS
$BODY$ DECLARE
var_affected INTEGER DEFAULT 0;
var_row scm_main.typ_connection_stations%ROWTYPE;
BEGIN
RAISE NOTICE 'Start...';
FOR var_row IN
SELECT DISTINCT v.vbvdata_station_id
FROM scm_main.tbl_vbvdata AS v
INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id =
p.packet_id and p.packet_connection_id = var_connection_id)
--WHERE v.vbvdata_packet_id IN
--( SELECT packet_id FROM scm_main.tbl_packet AS o_p WHERE
o_p.packet_connection_id = var_connection_id)
LOOP
RETURN NEXT var_row;
END LOOP;
RAISE NOTICE '...End';
RETURN;
END
$BODY$
LANGUAGE plpgsql STABLE STRICT
COST 100
ROWS 1000;
ALTER FUNCTION scm_main.fn_connection_stations(bigint)
OWNER TO postgres;
The query itself is very simple and runs when calling directly like this:
SELECT DISTINCT v.vbvdata_station_id
FROM scm_main.tbl_vbvdata AS v
INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id =
p.packet_id and p.packet_connection_id = 1219646)
I guess the problem must be lockibg of tables. But I absolutely don't understand what is the reason and what can be the solution.
Some time ago the commented part was working. But after a while the same problem was arised. I solved it by changing the query and replacing the condition (cascading query) with the inner join. But This time neither of them work!
Updated
I made it work again with a stupid change:
INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id = p.packet_id
and p.packet_connection_id = var_connection_id)`
was changed to:
INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id = p.packet_id)
where p.packet_connection_id in (select var_connection_id)
and worked perfectly.
Another interesting point is that even the below change didn't work and still hangs:
INNER JOIN scm_main.tbl_packet AS p ON (v.vbvdata_packet_id = p.packet_id)
where p.packet_connection_id = var_connection_id
Now I found a solution. But I want to know how it happening like this!
