Hakan Kocaman wrote:
Hello Folks,
first i want to apologize for my bad english :~)
we got here apparntly 2 issues with the above mentioned functionality:
1. I want to use functions with composite types as parameters,
which is no deal on our current production server(postgres 7.4.3)
but don't work on our upcoming production server(8.0.3).
A sample function is attached.
The function uses a composite type from a view, which declaration
is alos attached.
In this thread it is mentioned as a bug:
http://archives.postgresql.org/pgsql-admin/2005-08/msg00153.php
The question is if it's fixed in 8.0.4 or 8.1.
2. Using the mentioned function in a rule on the mentioned view
clashs on the fact, that we don't find a appropriate way for the syntax.
The rule is also attached.
CREATE OR REPLACE RULE update_produkte AS
ON UPDATE TO viewprodukte DO INSTEAD
SELECT fu_upd_viewprodukte(vp1.*, vp2.*) AS fu_upd_viewprodukte
FROM viewprodukte vp1, viewprodukte vp2
WHERE vp1.id = new.id AND vp2.id = old.id;
Well, as a short-term workaround, surely you could rewrite this to take
(new.id,old.id) as parameters instead?
I also can't reproduce your problem in 8.0.3 on my Debian-based system
here. I've attached my test-script - am I missing something?
--
Richard Huxton
Archonet Ltd
CREATE TABLE test_table (a int4, b text);
COPY test_table FROM stdin;
1 aaa
2 bbb
3 ccc
4 ddd
\.
CREATE VIEW test_view AS SELECT a,b FROM test_table WHERE (a % 2) = 1;
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;
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
