Re: Calling functions from Delete View (+ contrib/pgcrypto) = madness ?? - Mailing list pgsql-sql

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.Moran@IGT.com>
To: pgsql-sql@postgresql.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 -------