The following bug has been logged online: Bug reference: 4106 Logged by: Alexander Strotmann Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Windows XP / Windows 2003 Server Description: WHERE - clause in view works only sometimes Details:
Hello, we have this table: CREATE TABLE rollenhistorie ( pnr integer NOT NULL, rolle character varying NOT NULL, seit timestamp without time zone NOT NULL, bis timestamp without time zone, CONSTRAINT rollenhistorie_pkey PRIMARY KEY (pnr, seit) ) WITHOUT OIDS; and this view: CREATE OR REPLACE VIEW benutzer_mit_rolle_vw AS SELECT benutzer_ohne_alles_vw.pnr, benutzer_ohne_alles_vw.vorname, benutzer_ohne_alles_vw.nachname, benutzer_ohne_alles_vw.nutzerkennung, benutzer_ohne_alles_vw.passwort, benutzer_ohne_alles_vw.plz, benutzer_ohne_alles_vw.ort, benutzer_ohne_alles_vw.strasse, benutzer_ohne_alles_vw.hausnummer, benutzer_ohne_alles_vw.telefon_dienst, benutzer_ohne_alles_vw.email, benutzer_ohne_alles_vw.anzahl_tage_erinnerung, benutzer_ohne_alles_vw.gebietsschutz, rollenhistorie.rolle, rollenhistorie.seit, rollenhistorie.bis FROM benutzer_ohne_alles_vw NATURAL JOIN rollenhistorie WHERE rollenhistorie.bis IS NULL; So the view joins the upper table with another view and filters the sets in 'rollenhistorie' by taking only the sets where 'bis' is NULL. By questioning this view with this function: CREATE OR REPLACE FUNCTION get_user_and_role_plpgsql() RETURNS SETOF benutzer_mit_rolle_vw AS $BODY$ DECLARE rec RECORD; BEGIN FOR rec IN Select * from benutzer_mit_rolle_vw LOOP RETURN NEXT rec; END LOOP; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; we get sometimes results where 'bis' is not NULL! But it is not deterministic because in about 80% of request times the result is correct. The work-around for us is putting the 'WHERE bis IS NULL' in the function... Kind regards Alexander Strotmann -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
