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@pos
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 ot
On Mar 24, 2005, at 2:37 PM, Edmund Bacon wrote:
Sean Davis wrote:
Nice. Thanks for doing my work for me!
Yeah, well put it down to a certain amount of curiosity and a slack
period at work ...
I guess I will have to think about it more seriously.
It could be a slight bit complicated because my c
I figured it out:
Here's how to get an ID from the table below, based on input crypto data
(this essentially is the reverse of Jim's excellent cryto-to-ID solution
below):
--
-- selectFromCrypto.sql
--
-- Purpose: select from crypto table based on input crypto data in VARCHAR
format.
-- Returns
Sean Davis wrote:
Nice. Thanks for doing my work for me!
Yeah, well put it down to a certain amount of curiosity and a slack
period at work ...
I guess I will have to think about it more seriously.
It could be a slight bit complicated because my code is running under
mod_perl, so connections ar
On Mar 24, 2005, at 1:11 PM, Edmund Bacon wrote:
Sean Davis wrote:
Thanks. I thought about that a bit and it seems like it is highly
likely to be expensive for a single query (though I should probably
try it at some point). If I do find myself reformatting results
after response to user input
Jim,
I was thinking... In your excellent solution below, we select/decrypt and
return the crypto column based on an input Integer ID. This is good. But
what if we wanted to do the reverse?
That is, what if I want to select an ID based on input crypto data?
i.e., given my original table data
Sean Davis wrote:
Thanks. I thought about that a bit and it seems like it is highly
likely to be expensive for a single query (though I should probably
try it at some point). If I do find myself reformatting results after
response to user input (i.e., reusing the query), though, then your
sol
I would change the return type to TEXT, I believe your original example had it
as a varchar and I didn't change it.
Also, I believe that "under the hood" text does equal varchar.
Glad I could help
Jim
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: [EMAIL
Thank you, Jim. You rock!
This worked although I'm a bit confused:
Your function below says it returns VARCHAR, yet the variable that holds the
contents of my SELECT which we ultimately return is of type TEXT.
When I return the TEXT variable, it works. Does this mean TEXT = VARCHAR in
p
Thanks. I thought about that a bit and it seems like it is highly
likely to be expensive for a single query (though I should probably try
it at some point). If I do find myself reformatting results after
response to user input (i.e., reusing the query), though, then your
solution is likely to
give this a try
CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
_pid ALIAS FOR $1;
c text;
BEGIN
SELECT decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text) into c
FROM crypto
WHERE pid = _pid;
RET
Hello there,
What's the preferred and most efficient way to obtain PGCrypto encrypted
data from a plpgsql function?
1. Imagine the following simple table:
CREATE TABLE crypto (
pid SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_content BYTEA
);
2. Now insert the following 3 rows
Sometimes using a temp table is a better idea:
e.g.
-- start by creating a temp table 'tids' that hold the to_ids that
-- we are interested in.
SELECT to_id
INTO TEMP TABLE tids
FROM correlation
WHERE from_id = 1234
ORDER BY val DESC limit 100;
-- The following temp table makes use of t
14 matches
Mail list logo
