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

Jim,

Once again, your solution has worked wonders for me. I can now call the
Function via the DELETE RULE. Thank you very much. You rock!

By the way, as you may have noticed, I could not find ANY online
documentation on using PGCrypto + VIEWS + RULES + FUNCTIONs, all combined;
nor does the "purple book" (PostgreSQL by Korry Douglas) go into these
details... So, if I may ask, how did you acquire such extensive knowledge
on these topics used in conjunction? Online via the forum, like me?

Anyway, by far, this Posgres SQL forum is the best forum I've encountered
for finding answers to questions like mine below. Much better than the
Hackers or Admin forums it seems.

Thanks all!

-Michael Moran

_____

From: Jim Buttafuoco [mailto:jim(at)contactbda(dot)com]
Sent: Thu 3/24/2005 7:44 PM
To: Moran.Michael; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] 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 -------