Thread: 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{\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
Re: Calling functions from Delete View (+ contrib/pgcrypto) = madness ??
From
"Jim Buttafuoco"
Date:
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 -------