Re: infinite recursion detected in rules for relation

On Thu, Feb 12, 2015 at 10:48 AM, pinker <pinker(at)onet(dot)eu> wrote:

> I wanted to set a rule:
>
> CREATE RULE "_RETURN" AS
> ON SELECT * from backend.test
> DO INSTEAD
> SELECT * FROM backend.test WHERE who='Me';
>
> When I'm trying to do anything on the table I get following error:
>
> ERROR: infinite recursion detected in rules for relation "backend.test"
>
> ********** Błąd **********
>
> ERROR: infinite recursion detected in rules for relation "backend.test"
> Stan SQL: 42P17
>
> Is there any way to avoid that? Maybe there exist some other approaches
> that could be useful ?

​I'm not totally sure why you want to do the above. If I needed such a
thing and "backend.test" already exists, I would rename "backend.test" to
something like "backend.test__x" and then create a view like:

CREATE VIEW backend.test AS
SELECT * FROM bachend.test__x WHERE who='Me'
WITH CHECK OPTION.

The WITH CHECK OPTION will stop people from doing a INSERT or UPDATE which
did not have "who" equal to 'Me'. I don't know if you would need this.

I would then GRANT appropriate authority to the VIEW and remove it from
"backend.test__x"​.

--
He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown