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