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:[EMAIL PROTECTED]
Sent: Thu 3/24/2005 7:44 PM
To: Moran.Michael; pgsql-sql@postgresql.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" <[EMAIL PROTECTED]>
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('', 'password',
> 'aes') );
> insert into crypto VALUES (2, 'test2', encrypt('', 'password',
> 'aes') );
> insert into crypto VALUES (3, 'test3', encrypt('', 'password',
> 'aes') );
> insert into crypto VALUES (4, 'test4', encrypt('', 'password',
> 'aes') );
> insert into crypto VALUES (5, 'test5', encrypt('', '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 |
> 2 | test2 |
> 3 | test3 |
> 4 | test4 |
> 5 | test5 |
> (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 = ;
> 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 | 2
