Обсуждение: Calling a function from a rule?

I am missing something about how to properly create a rule.

Thanks to the helpful folks on this list, I am looking a creating some
update able views. So, looks like I need to create a rule and a function
for this. Here is what I am trying as a test.



DROP TRIGGER v_trig_test ON test;

CREATE OR REPLACE FUNCTION v_trig_test() RETURNS trigger AS $$
use 5.010;
use strict;
use warnings;
use Data::Dumper qw(Dumper);

my $new =  Dumper $_TD->{new};
my $old =  Dumper $_TD->{old};

elog(NOTICE, "old = $old" );
elog(NOTICE, "new = $new" );

return;

$$ LANGUAGE plperlu;


CREATE RULE "_RETURN" AS
    ON UPDATE TO purchase_view
    DO INSTEAD
        SELECT * FROM v_trig_test;


But the select line in the create rule seems to be a syntax error.

Here is my thinking. I have done functions called from triggers, and I am
modeling this after that experience, so I think I need the rule to call the
function, correct?
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On Wed, Mar 04, 2020 at 08:05:06AM -0500, stan wrote:
> I am missing something about how to properly create a rule.
> 
> Thanks to the helpful folks on this list, I am looking a creating some
> update able views. So, looks like I need to create a rule and a function
> for this. Here is what I am trying as a test.
> 
> 
> 
> DROP TRIGGER v_trig_test ON test;
> 
> CREATE OR REPLACE FUNCTION v_trig_test() RETURNS trigger AS $$
> use 5.010;
> use strict;
> use warnings;
> use Data::Dumper qw(Dumper);
> 
> my $new =  Dumper $_TD->{new};
> my $old =  Dumper $_TD->{old};
> 
> elog(NOTICE, "old = $old" );
> elog(NOTICE, "new = $new" );
> 
> return;
> 
> $$ LANGUAGE plperlu;
> 
> 
> CREATE RULE "_RETURN" AS
>     ON UPDATE TO purchase_view
>     DO INSTEAD
>         SELECT * FROM v_trig_test;
> 
> 
> But the select line in the create rule seems to be a syntax error.
> 
> Here is my thinking. I have done functions called from triggers, and I am
> modeling this after that experience, so I think I need the rule to call the
> function, correct?

Looks like the issue has to do with defining the return type of the
function. I corrected the SELECT in the rule to:

        SELECT * FROM v_trig_test()

Whihc got me as far as a complaint about the return type of the function,
so now the parser understands that I am trying to call a function. What
should my function return?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On 04/03/2020 14:05, stan wrote:
> I am missing something about how to properly create a rule.

What you are missing is that you should not ever use RULEs.

> Thanks to the helpful folks on this list, I am looking a creating some
> update able views. So, looks like I need to create a rule and a function
> for this.

Use an INSTEAD OF trigger on the view unless it's a simple view in which
case it "just works".
-- 
Vik Fearing

On Wed, Mar 04, 2020 at 03:04:34PM +0100, Vik Fearing wrote:
> On 04/03/2020 14:05, stan wrote:
> > I am missing something about how to properly create a rule.
> 
> What you are missing is that you should not ever use RULEs.
> 
> > Thanks to the helpful folks on this list, I am looking a creating some
> > update able views. So, looks like I need to create a rule and a function
> > for this.
> 
> Use an INSTEAD OF trigger on the view unless it's a simple view in which
> case it "just works".

OK, I certainly believe you have more knowledge than I do here Just let me
make certain what I am trying to do fits that model.

I am trying to create an "update-able view" on a view that dereferences a
number of keys to foreign tables to make the view more "human friendly". so
it is going to need to look up those keys before allowing the insert into
the base table(s).

Can you please explain the difference between triggers and rules? 

I have some experience with using triggers in other situations, but the
documentation for update-able views seemed to point me to rules.

Tanks.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin