Thread: Issue with functions in Rule

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.

Also attached is the create statement for the base_table tblprodukte.

I know this looks all very denormalized... hmmm... and in fact it is for reasons
i can't describe in short terms, so please overlook this uglyness.

If anyone got a clue or pointer how this could be done, i would be very thankfull.

Best regards


Hakan Kocaman

Software-Developer
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98

Email: hakan.kocaman@digame.de

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;

Hello Richard,

thanks for your response.
Your testcase does indeed make no problems.
I stripped down my case to be as simple as yours
and it worked too.

Now i try to put the things in it, that are usefull
for me (Validation in the update-function and Conversion
in the view).

I hope (or better not :~))i can then put a testcase together,
that fails on a specific point.

I'll be back with more information.

Best regards

Hakan Kocaman

Software-Developer
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98

Email: hakan.kocaman@digame.de



> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: Wednesday, October 19, 2005 9:59 AM
> To: Hakan Kocaman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Issue with functions in Rule
>
>
> 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
>