Calling Functions from Delete Rule (+ contrib/pgcrypto) = madness ?? - Mailing list pgsql-hackers

Hello there,
I have a View with a Delete rule and I would like the Delete rule to call a
function (and pass-in a few of the the underlying View's/Table's column
values). How do you do this?
When I do it, I keep getting the following error:
ERROR:  function expression in FROM may not refer to other relations of same
query level
This is my new Delete View that attempts to call a Function but yields the
above-mentioned error:
CREATE RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD   --Original (working) code:   --  DELETE FROM crypto   -- WHERE id = OLD.id;
   --NEW (non-working) code:   select * from func_delFromCrypto( crypto.id, encode( decrypt(
crypto.crypted_content, decode ('password'::text, 'escape'::text),
'aes'::text), 'escape'::text) );

The above may seem complicated, but the crypto portion DOES work (as you'll
see below), and I've broken down the steps below so that it's easy to see
what's going on:
1. Imagine the following simple table:
CREATE TABLE crypto (   id SERIAL PRIMARY KEY,   title VARCHAR(50),   crypted_content BYTEA
);
2. Imagine the following simple working View:
CREATE VIEW crypto_view AS SELECT   id,   title,   --Decrypt the BYTEA column and convert result to TEXT type:
encode(decrypt(crypted_content,decode('password','escape'::text),
 
'aes'::text), 'escape'::text) as crypted_content FROM   crypto;
3. Imagine my original, simple Delete Rule (that works -- it was my original
version prior to changing it to call the Function):
CREATE RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD   DELETE FROM crypto   WHERE id = OLD.id;
4. Let's load (and encrypt) some test data into the above-mentioned table:
insert into crypto VALUES (1, 'test1', encrypt('11112222', 'password',
'aes') );
insert into crypto VALUES (2, 'test2', encrypt('22223333', 'password',
'aes') );
insert into crypto VALUES (3, 'test3', encrypt('33334444', 'password',
'aes') );
insert into crypto VALUES (4, 'test4', encrypt('44445555', 'password',
'aes') );
insert into crypto VALUES (5, 'test5', encrypt('55556666', 'password',
'aes') );

5. Let's SELECT from the table to see its contents (note the encrypted
values):
select * from crypto;id | title |                      crypted_content
----+-------+------------------------------------------------------------ 1 | test1 |
\026\206I93\327\315\376t\243\006~J\177{\3012 | test2 | \257\212\260\321\217\274c\210d \361\225\212\327\317\206 3 |
test3| 6\345:\224dp\002\206<\007k\344\302\347V\214 4 | test4 | VH)\023\303\0239\363\323\362\22734\204R\357 5 | test5 |
\216Np\235\026\362\277\246\026\027\221\266\021\361\224\256
(5 rows)
6. Let's run the View (which nicely decrypts the encrypted columns):
# select * from crypto_view;id | title | crypted_content
----+-------+----------------- 1 | test1 | 11112222 2 | test2 | 22223333 3 | test3 | 33334444 4 | test4 | 44445555 5 |
test5| 55556666
 
(5 rows)
7. Let's test the old DELETE RULE (the one that doesn't call a function,
hence works):
delete from crypto_view where crypted_content = 55556666;
DELETE 1
Check that the DELETE RULE worked (there is one less row of data now):
# select * from crypto_view;id | title | crypted_content
----+-------+----------------- 1 | test1 | 11112222 2 | test2 | 22223333 3 | test3 | 33334444 4 | test4 | 44445555
(4 rows)

It works! Pretty straight forward, right?
Now let's make things a little more interesting...
8. Now here is the following (working) function that I want my rule to call:
CREATE OR REPLACE FUNCTION func_delFromCrypto(INTEGER, VARCHAR)
RETURNS INTEGER
AS '
DECLARE   _id             ALIAS FOR $1;   _crypto_data    ALIAS FOR $2;   ret             INTEGER:=0;
BEGIN   DELETE FROM crypto WHERE id = _id AND encode( decrypt( crypted_content,
decode(''password''::text, ''escape''::text), ''aes''::text),
''escape''::text) = _crypto_data;   GET DIAGNOSTICS ret = ROW_COUNT;   RETURN ret;
END;
' LANGUAGE 'plpgsql';
9. The function works, stand-alone, as you can see below:
# select * from func_delFromCrypto(4, '44445555');func_delfromcrypto
--------------------                 1
(1 row)
Check the output (there is one less row now):
# select * from crypto_view;id | title | crypted_content
----+-------+----------------- 1 | test1 | 11112222 2 | test2 | 22223333 3 | test3 | 33334444
(3 rows)
So the function works, stand-alone. But how can I get my Delete Rule to call
it?
I want my Delete Rule to pass in the underlying ID (the primary key) and the
crypted_content into the Function (like you can when calling the Function
stand-alone).
Once again, here is my new DELETE RULE that calls the function (and fails):
CREATE RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD   --Original (working) code:   --  DELETE FROM crypto   -- WHERE id = OLD.id;
   --NEW (non-working) code:   select * from func_delFromCrypto( crypto.id, encode( decrypt(
crypto.crypted_content, decode ('password'::text, 'escape'::text),
'aes'::text), 'escape'::text) );

I suspect there is a syntax error or something, as I don't have access to
the underlying colums "crypto.id" and "crypto.crypted_content". Any help is
greatly appreciated.
Thank you kindly in advance,
Michael Moran