Hi all,
take a look at this simple function and view:
CREATE OR REPLACE FUNCTION sp_connected_test ( INTEGER )
RETURNS BOOLEAN AS'
DECLARE
a_id_user ALIAS FOR $1;
BEGIN
PERFORM *
FROM v_current_connection
WHERE id_user = a_id_user;
IF NOT FOUND THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
CREATE VIEW v_current_connection_test
AS SELECT ul.id_user, cc.connected
FROM current_connection cc,
user_login ul
WHERE cc.id_user = ul.id_user AND
connected = TRUE;
SELECT * FROM v_current_connection_test WHERE sp_connected_test(id_user) =
FALSE;
this line shall produce no row, but randomly does.
If I put a RAISE NOTICE before RETURN NULL with the id_user I notice that
the function is called on records present on user_login but discarged because
the join with current_connectin have connected = FALSE!
I can work_around the problem rewriting the view:
CREATE VIEW v_current_connection_test
AS SELECT cc.id_user, cc.connected
FROM current_connection cc,
user_login ul
WHERE cc.id_user = ul.id_user AND
connected = TRUE;
Regards
Gaetano Mendola
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
