Re: Calling functions from Delete View (+ contrib/pgcrypto) = madness ??

Mike,

I posted this RULE also on hackers

CREATE or replace RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD
(
select func_delFromCrypto( OLD.id,OLD.crypted_content);
);

Jim

---------- Original Message -----------
From: "Moran.Michael" <Michael(dot)Moran(at)IGT(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Sent: Thu, 24 Mar 2005 17:30:33 -0800
Subject: [SQL] Calling functions from Delete View (+ contrib/pgcrypto) = madness ??

> 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{\301
> 2 | 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
------- End of Original Message -------