Hello!
If a view which calls a function is LEFT JOINed to a table but not all
result rows are matched by some criteria, then the function is called
for each row of the view nevertheless.
It is interesting, that the same query without using a view calls the
function only for those rows wich are really in the result set.
This discrepancy is of course Not-A-Good-Thing(tm) if the function has side
effects or is very expensive.
Note that this seems to happen only for left joins, not for a inner join.
The following example illustrates this by using a "noisy" function.
-----------------------------------------------
CREATE TABLE t1 (id int, t1val text);
CREATE TABLE t2 (id int, t2val int);
-- insert some test values
INSERT INTO t1 SELECT i, 'foo bar ' || i FROM generate_series(0, 20) i;
INSERT INTO t2 SELECT i, i*i FROM generate_series(0, 20) i;
-- create a noisy function
CREATE OR REPLACE FUNCTION notice(id int, val int) RETURNS int AS $$
BEGIN
RAISE NOTICE 'function called for (%, %)', id, val;
RETURN id;
END;
$$ LANGUAGE plpgsql;
-- direct query
SELECT t1.*, t2.*, notice(t2.id, t2.t2val)
FROM t1 LEFT JOIN t2 USING (id) WHERE id < 10;
-- result: only 10 NOTICE messages
-- same query with a view
CREATE VIEW t2v AS SELECT *, notice(id, t2val) FROM t2;
SELECT t1.*, t2v.* FROM t1 LEFT JOIN t2v USING (id) WHERE id < 10;
-- result: 20 NOTICE messages, 10 to much
-----------------------------------------------
I hope, this is really a bug and not something I didn't understand :-)
Best Regards
Andreas Heiduk
______________________________________________________________
Verschicken Sie romantische, coole und witzige Bilder per SMS!
Jetzt bei WEB.DE FreeMail: http://f.web.de/?mc=021193
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
