This is a multi-part message in MIME format.
--------------070505080700080901050806
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Earlier today Hakan Kocaman (Hakan.Kocaman (AT) digame (DOT) de) posted a query on
general "Issue with functions in Rule".

Turned out the problem is with a function that takes a compound type
based on a view with ORDER BY. If you add/remove ORDER BY to the view
definition in the attached it doesn't recognise the type (I think)

The error is different in 8.0.x and 8.1.x but occurs in both.

--
Richard Huxton
Archonet Ltd

--------------070505080700080901050806
Content-Type: text/x-sql;
name="view_rectype_fn.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="view_rectype_fn.sql"


CREATE TABLE test_table (a int4, b text);
COPY test_table FROM stdin;
1 aaa
2 bbb
3 ccc
4 ddd
\.

CREATE OR REPLACE VIEW test_view AS SELECT a,b FROM test_table WHERE (a % 2) = 1 ORDER BY a;


CREATE OR REPLACE FUNCTION test_upd_fn(test_view, test_view) RETURNS boolean AS $$
BEGIN
RAISE NOTICE 'test_upd_fn(a1=%, b1=%, a2=%, b2=%)', $1.a,$1.b, $2.a,$2.b;
UPDATE test_table SET b=b || 'x' WHERE a=$1.a;
RETURN true;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE RULE my_upd_rule AS ON UPDATE TO test_view
DO INSTEAD
SELECT test_upd_fn(v1.*, v2.*) FROM test_view v1, test_view v2 WHERE v1.a=NEW.a AND v2.a=OLD.a;


SELECT * FROM test_table;
SELECT * FROM test_view;

UPDATE test_view SET b='ccc2' WHERE a=3;

SELECT * FROM test_view;


--------------070505080700080901050806
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--------------070505080700080901050806--

Richard Huxton <dev (AT) archonet (DOT) com> writes:
Fixed in CVS tip --- will look at the 8.0 branch later.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend