Re: infinite recursion detected in rules for relation

[John McKown <john.archie.mckown@xxxxxxxxx>]

On Thu, Feb 12, 2015 at 10:48 AM, pinker <pinker@xxxxxxx> 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