Re: [SQL] Self-referencing table question

[Edmund Bacon]

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 the primary key on
-- the correlation table, and the stated goal from the original
-- question that:
-- from_id > to_id
-- and from_id in (tids.to_id)
--and to_id in (tids.to_id)
 SELECT t1.to_id AS from_id, t2.to_id
   INTO TEMP TABLE from_to
   FROM tids t1, tids t2
  WHERE t1.to_id > t2.to_id;
-- Now we can use the from_to table as an index into the correlation
-- table.
 SELECT c.from_id, c.to_id, c.val
   FROM from_to
   JOIN correlation c USING(from_id, to_id)
  WHERE val > 0.5;
The explain analyze for the final select works out to:
Nested Loop  (cost=0.00..50692.00 rows=8488 width=16) (actual 
time=0.171..150.095 rows=2427 loops=1)
  ->  Seq Scan on from_to  (cost=0.00..79.38 rows=5238 width=8) (actual 
time=0.006..7.660 rows=4950 loops=1)
  ->  Index Scan using correlation_pkey on correlation c  
(cost=0.00..9.63 rows=2 width=16) (actual time=0.024..0.025 rows=0 
loops=4950)
Index Cond: (("outer".from_id = c.from_id) AND ("outer".to_id = 
c.to_id))
Filter: (val > 0.5::double precision)
Total runtime: 152.261 ms

Richard Huxton wrote:
Sean Davis wrote:
I answer my own question, if only for my own records.  The following  
query is about 5-6 times faster than the original.  Of course, if  
anyone else has other ideas, I'd be happy to hear them.

Sean
explain analyze select from_id,to_id,val from exprsdb.correlation 
where  from_id in (select to_id from exprsdb.correlation where 
from_id=2424  order by val desc limit 100) and to_id in (select to_id 
from  exprsdb.correlation where from_id=2424 order by val desc limit 
100) and  val>0.6 and to_id
Might not be any faster, but you can do this as a self-join with 
subquery:

SELECT c1.from_id, c1.to_id, c1.val
FROM
  correlation c1,
  (
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
  ) AS c2
  (
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
  ) AS c3
WHERE
  c1.from_id = c2.to_id
  AND c1.to_id = c3.to_id
  AND c1.val > 0.5
  AND c1.to_id < from_id
;
I think PG should be smart enough nowadays to figure out these two 
queries are basically the same.

--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

[SQL] Funtions + plpgsql + contrib/pgcrypto = ??

[Moran.Michael]

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 of data:

INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD', 'password',
'aes'));
INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE', 'password',
'aes'));
INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF', 'password',
'aes'));

3. Using the psql tool, selecting * from the crypto table yields the
following:
 
# select * from crypto;
 id | title |crypted_content
+---+
  1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215
  2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017
  3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266

Pretty straight forward, right? 
 
Now how about doing this in a simple plpgsql Function. That's where we
encounter problems. I want to get DECRYPTED data based on an input ID. So...

4. Imagine the following simple plpgsql function (note I'm trying to decrypt
the table's encrypted BYTEA column into a decrypted VARCHAR for return):

CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
crypto_cursor   CURSOR (input INTEGER) FOR SELECT
encode(decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id =
input;
crypto  crypto.crypted_content%TYPE;
tid ALIAS FOR $1;
 
BEGIN
OPEN crypto_cursor( tid );
LOOP
FETCH crypto_cursor INTO crypto;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE crypto_cursor;
RETURN ( encode(crypto, ''escape''::text)::VARCHAR );
END;
' LANGUAGE 'plpgsql';

5. When I use the above function (in the tool, psql) to get the decrypted
contents for ID = 2, it says I get 1 row returned, but the contents are
blank:
 
# select * from selectFromCrypto(1);
 selectfromcrypto1
---
 

(1 row)

Notice the blank row returned... So what am I doing wrong?
 
I suspect it has something to do with
converting/encoding/decoding/decrypting the BYTEA column for return... but
what is the problem with the above Function?
 
I'm using PostgreSQL 7.4.6, on a Fedora Core 1 system.

 
Best regards and thank you very much in advance,
Michael Moran


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??

[Jim Buttafuoco]

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;

   RETURN c;
END;
' LANGUAGE 'plpgsql'; 




-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 08:41:34 -0800
Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??

> 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 of data:
> 
> INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD', 'password',
> 'aes'));
> INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE', 'password',
> 'aes'));
> INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF', 'password',
> 'aes'));
> 
> 3. Using the psql tool, selecting * from the crypto table yields the
> following:
> 
> # select * from crypto;
>  id | title |crypted_content
> +---+
>   1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215
>   2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017
>   3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266
> 
> Pretty straight forward, right?
> 
> Now how about doing this in a simple plpgsql Function. That's where we
> encounter problems. I want to get DECRYPTED data based on an input ID. So...
> 
> 4. Imagine the following simple plpgsql function (note I'm trying to decrypt
> the table's encrypted BYTEA column into a decrypted VARCHAR for return):
> 
> CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
> RETURNS VARCHAR
> AS '
> DECLARE
> crypto_cursor   CURSOR (input INTEGER) FOR SELECT
> encode(decrypt(crypted_content, decode(''password''::text,
> ''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id =
> input;
> crypto  crypto.crypted_content%TYPE;
> tid ALIAS FOR $1;
> 
> BEGIN
> OPEN crypto_cursor( tid );
> LOOP
> FETCH crypto_cursor INTO crypto;
> EXIT WHEN NOT FOUND;
> END LOOP;
> CLOSE crypto_cursor;
> RETURN ( encode(crypto, ''escape''::text)::VARCHAR );
> END;
> ' LANGUAGE 'plpgsql';
> 
> 5. When I use the above function (in the tool, psql) to get the decrypted
> contents for ID = 2, it says I get 1 row returned, but the contents are
> blank:
> 
> # select * from selectFromCrypto(1);
>  selectfromcrypto1
> ---
> 
> (1 row)
> 
> Notice the blank row returned... So what am I doing wrong?
> 
> I suspect it has something to do with
> converting/encoding/decoding/decrypting the BYTEA column for return... but
> what is the problem with the above Function?
> 
> I'm using PostgreSQL 7.4.6, on a Fedora Core 1 system.
> 
> Best regards and thank you very much in advance,
> Michael Moran
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
--- End of Original Message ---


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Self-referencing table question

[Sean Davis]

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 be very useful.

Sean
On Mar 24, 2005, at 11:13 AM, Edmund Bacon wrote:
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 the primary key on
-- the correlation table, and the stated goal from the original
-- question that:
-- from_id > to_id
-- and from_id in (tids.to_id)
--and to_id in (tids.to_id)
 SELECT t1.to_id AS from_id, t2.to_id
   INTO TEMP TABLE from_to
   FROM tids t1, tids t2
  WHERE t1.to_id > t2.to_id;
-- Now we can use the from_to table as an index into the correlation
-- table.
 SELECT c.from_id, c.to_id, c.val
   FROM from_to
   JOIN correlation c USING(from_id, to_id)
  WHERE val > 0.5;
The explain analyze for the final select works out to:
Nested Loop  (cost=0.00..50692.00 rows=8488 width=16) (actual 
time=0.171..150.095 rows=2427 loops=1)
  ->  Seq Scan on from_to  (cost=0.00..79.38 rows=5238 width=8) 
(actual time=0.006..7.660 rows=4950 loops=1)
  ->  Index Scan using correlation_pkey on correlation c  
(cost=0.00..9.63 rows=2 width=16) (actual time=0.024..0.025 rows=0 
loops=4950)
Index Cond: (("outer".from_id = c.from_id) AND ("outer".to_id 
= c.to_id))
Filter: (val > 0.5::double precision)
Total runtime: 152.261 ms

Richard Huxton wrote:
Sean Davis wrote:
I answer my own question, if only for my own records.  The following 
 query is about 5-6 times faster than the original.  Of course, if  
anyone else has other ideas, I'd be happy to hear them.

Sean
explain analyze select from_id,to_id,val from exprsdb.correlation 
where  from_id in (select to_id from exprsdb.correlation where 
from_id=2424  order by val desc limit 100) and to_id in (select 
to_id from  exprsdb.correlation where from_id=2424 order by val desc 
limit 100) and  val>0.6 and to_id
Might not be any faster, but you can do this as a self-join with 
subquery:

SELECT c1.from_id, c1.to_id, c1.val
FROM
  correlation c1,
  (
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
  ) AS c2
  (
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
  ) AS c3
WHERE
  c1.from_id = c2.to_id
  AND c1.to_id = c3.to_id
  AND c1.val > 0.5
  AND c1.to_id < from_id
;
I think PG should be smart enough nowadays to figure out these two 
queries are basically the same.

--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of 
broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??

[Moran.Michael]

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
plpgsql Functions?
 
Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it
seems logical to try to match the declared return type)... it fails
 
So, this works:return c;
This doesn't:  return c::VARCHAR;
 
I always thought matching my return type to my funtion's RETURN declaration
is logical... but now I know that if want a VARCHAR, I gotta manipulate it
as a TEXT within my function when using PGCrypto. Any idea why?
 
Thank again, Jim!
 

  _  

From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
Sent: Thu 3/24/2005 9:14 AM
To: Moran.Michael; pgsql-sql@postgresql.org
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??



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; 

   RETURN c; 
END; 
' LANGUAGE 'plpgsql'; 




-- Original Message --- 
From: "Moran.Michael" <[EMAIL PROTECTED]> 
To: pgsql-sql@postgresql.org 
Sent: Thu, 24 Mar 2005 08:41:34 -0800 
Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 

> 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 of data: 
> 
> INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD',
'password', 
> 'aes')); 
> INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE',
'password', 
> 'aes')); 
> INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF',
'password', 
> 'aes')); 
> 
> 3. Using the psql tool, selecting * from the crypto table yields the 
> following: 
> 
> # select * from crypto; 
>  id | title |crypted_content 
> +---+ 
>   1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215 
>   2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017 
>   3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266 
> 
> Pretty straight forward, right? 
> 
> Now how about doing this in a simple plpgsql Function. That's where we 
> encounter problems. I want to get DECRYPTED data based on an input ID.
So... 
> 
> 4. Imagine the following simple plpgsql function (note I'm trying to
decrypt 
> the table's encrypted BYTEA column into a decrypted VARCHAR for return): 
> 
> CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER) 
> RETURNS VARCHAR 
> AS ' 
> DECLARE 
> crypto_cursor   CURSOR (input INTEGER) FOR SELECT 
> encode(decrypt(crypted_content, decode(''password''::text, 
> ''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id
= 
> input; 
> crypto  crypto.crypted_content%TYPE; 
> tid ALIAS FOR $1; 
> 
> BEGIN 
> OPEN crypto_cursor( tid ); 
> LOOP 
> FETCH crypto_cursor INTO crypto; 
> EXIT WHEN NOT FOUND; 
> END LOOP; 
> CLOSE crypto_cursor; 
> RETURN ( encode(crypto, ''escape''::text)::VARCHAR ); 
> END; 
> ' LANGUAGE 'plpgsql'; 
> 
> 5. When I use the above function (in the tool, psql) to get the decrypted 
> contents for ID = 2, it says I get 1 row returned, but the contents are 
> blank: 
> 
> # select * from selectFromCrypto(1); 
>  selectfromcrypto1 
> --- 
> 
> (1 row) 
> 
> Notice the blank row returned... So what am I doing wrong? 
> 
> I suspect it has something to do with 
> converting/encoding/decoding/decrypting the BYTEA column for return... but

> what is the problem with the above Function? 
> 
> I'm using PostgreSQL 7.4.6, on a Fedora Core 1 system. 
> 
> Best regards and thank you very much in advance, 
> Michael Moran 
> 
> ---(end of broadcast)--- 
> TIP 4: Don't 'kill -9' the postmaster 
--- End of Original Message --- 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??

[Jim Buttafuoco]

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 PROTECTED], pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 09:43:18 -0800
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??

> 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
> plpgsql Functions?
> 
> Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it
> seems logical to try to match the declared return type)... it fails
> 
> So, this works:return c;
> This doesn't:  return c::VARCHAR;
> 
> I always thought matching my return type to my funtion's RETURN declaration
> is logical... but now I know that if want a VARCHAR, I gotta manipulate it
> as a TEXT within my function when using PGCrypto. Any idea why?
> 
> Thank again, Jim!
> 
>   _
> 
> From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
> Sent: Thu 3/24/2005 9:14 AM
> To: Moran.Michael; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
> 
> 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;
> 
>RETURN c; 
> END; 
> ' LANGUAGE 'plpgsql';
> 
> -- Original Message --- 
> From: "Moran.Michael" <[EMAIL PROTECTED]> 
> To: pgsql-sql@postgresql.org 
> Sent: Thu, 24 Mar 2005 08:41:34 -0800 
> Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
> 
> > 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 of data: 
> > 
> > INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD',
> 'password', 
> > 'aes')); 
> > INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE',
> 'password', 
> > 'aes')); 
> > INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF',
> 'password', 
> > 'aes')); 
> > 
> > 3. Using the psql tool, selecting * from the crypto table yields the 
> > following: 
> > 
> > # select * from crypto; 
> >  id | title |crypted_content 
> > +---+ 
> >   1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215 
> >   2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017 
> >   3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266 
> > 
> > Pretty straight forward, right? 
> > 
> > Now how about doing this in a simple plpgsql Function. That's where we 
> > encounter problems. I want to get DECRYPTED data based on an input ID.
> So... 
> > 
> > 4. Imagine the following simple plpgsql function (note I'm trying to
> decrypt 
> > the table's encrypted BYTEA column into a decrypted VARCHAR for return): 
> > 
> > CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER) 
> > RETURNS VARCHAR 
> > AS ' 
> > DECLARE 
> > crypto_cursor   CURSOR (input INTEGER) FOR SELECT 
> > encode(decrypt(crypted_content, decode(''password''::text, 
> > ''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id
> = 
> > input; 
> > crypto  crypto.crypted_content%TYPE; 
> > tid ALIAS FOR $1; 
> > 
> > BEGIN 
> > OPEN crypto_cursor( tid ); 
> > LOOP 
> > FETCH crypto_cursor INTO crypto; 
> > EXIT WHEN NOT FOUND; 
> > END LOOP; 
> > CLOSE crypto_cursor; 
> > RETURN ( encode(crypto, ''escape''::text)::VARCHAR ); 
> > END; 
> > ' LANGUAGE 'plpgsql'; 
> > 
> > 5. When I use the above function (in the tool, psql) to get the decrypted 
> > contents for ID = 2, it says I get 1 row returned, but the contents are 
> > blank: 
> > 
> > # select * from selectFromCrypto(1); 
> >  selectfromcrypto1 
> > --- 
> > 
> > (1 row) 
> > 
> > Notice the blank row returned... So what am I doing wrong? 
> > 
> > I suspect it has something to do with 
> > converting/encoding/decoding/decrypting the BYTEA column for return... but
> 
> > what is the problem with the above Function? 
> > 
> > I'm using PostgreSQL 7.4.6, on a Fedora Core 1 system. 
> > 
> > Best regards and thank you very much in advance, 
> > Michael Moran 
> > 
> > ---(end of broadcast)

Re: [SQL] Self-referencing table question

[Edmund Bacon]

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 
solution is likely to be very useful.

You might think so, however, consider:
$ cat temptable.sql
 SELECT to_id
   INTO TEMP TABLE tids
   FROM correlation
  WHERE from_id  = 1234
  ORDER BY val DESC limit 100;
 SELECT t1.to_id AS from_id, t2.to_id
   INTO TEMP TABLE from_to
   FROM tids t1, tids t2
  WHERE t1.to_id > t2.to_id;
 SELECT c.from_id, c.to_id, c.val
   FROM from_to
   JOIN correlation c USING(from_id, to_id)
  WHERE val > 0.5
  order by from_id, to_id;
$ cat subselect.sql
select
   from_id, to_id, val
   from correlation
   where from_id in (select to_id from correlation
where from_id = 1234
order by val desc limit 100)
   and to_id in (select to_id from correlation
where from_id = 1234
order by val desc limit 100)
   and from_id > to_id
   and val > 0.5
   order by from_id, to_id;
$ psql -q -c "select count(1) from correlation" test
 count  
--
6400
(1 row)

$ time psql -q -f subselect.sql test | md5sum -
f289b259ce8a2a4a45f9e0eca6e31957  -
real0m3.093s
user0m0.000s
sys0m0.010s
$ time psql -q -f temptable.sql test | md5sum -
f289b259ce8a2a4a45f9e0eca6e31957  -
real0m0.238s
user0m0.000s
sys0m0.010s
$ time psql -q -f subselect.sql test | md5sum -
f289b259ce8a2a4a45f9e0eca6e31957  -
real0m1.945s
user0m0.010s
sys0m0.010s
$ time psql -q -f subselect.sql test | md5sum -
f289b259ce8a2a4a45f9e0eca6e31957  -
real0m1.949s
user0m0.010s
sys0m0.010s
$ time psql -q -f subselect.sql test | md5sum -
f289b259ce8a2a4a45f9e0eca6e31957  -
real0m1.953s
user0m0.010s
sys0m0.000s
$ time psql -q -f temptable.sql test | md5sum -
f289b259ce8a2a4a45f9e0eca6e31957  -
real0m0.237s
user0m0.020s
sys0m0.000s
$
Note that the subselect version takes about 10 times as long as the 
temptable version, and does not seem to be dependent on what data might 
be cached.

I added the sort so that the results would be in the same order - you 
can see that the two query sets  are producing the same output (at least 
md5sum thinks they are the same).

One thing to be aware of is the size of your returned data set - If it's 
fairly large, then the transfer time from your web-server to the pgsql 
box might overwhelm any "small" optimization in query time. 

Sean
On Mar 24, 2005, at 11:13 AM, Edmund Bacon wrote:
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 the primary key on
-- the correlation table, and the stated goal from the original
-- question that:
-- from_id > to_id
-- and from_id in (tids.to_id)
--and to_id in (tids.to_id)
 SELECT t1.to_id AS from_id, t2.to_id
   INTO TEMP TABLE from_to
   FROM tids t1, tids t2
  WHERE t1.to_id > t2.to_id;
-- Now we can use the from_to table as an index into the correlation
-- table.
 SELECT c.from_id, c.to_id, c.val
   FROM from_to
   JOIN correlation c USING(from_id, to_id)
  WHERE val > 0.5;
The explain analyze for the final select works out to:
Nested Loop  (cost=0.00..50692.00 rows=8488 width=16) (actual 
time=0.171..150.095 rows=2427 loops=1)
  ->  Seq Scan on from_to  (cost=0.00..79.38 rows=5238 width=8) 
(actual time=0.006..7.660 rows=4950 loops=1)
  ->  Index Scan using correlation_pkey on correlation c  
(cost=0.00..9.63 rows=2 width=16) (actual time=0.024..0.025 rows=0 
loops=4950)
Index Cond: (("outer".from_id = c.from_id) AND ("outer".to_id 
= c.to_id))
Filter: (val > 0.5::double precision)
Total runtime: 152.261 ms

Richard Huxton wrote:
Sean Davis wrote:
I answer my own question, if only for my own records.  The 
following  query is about 5-6 times faster than the original.  Of 
course, if  anyone else has other ideas, I'd be happy to hear them.

Sean
explain analyze select from_id,to_id,val from exprsdb.correlation 
where  from_id in (select to_id from exprsdb.correlation where 
from_id=2424  order by val desc limit 100) and to_id in (select 
to_id from  exprsdb.correlation where from_id=2424 order by val 
desc limit 100) and  val>0.6 and to_id
Might not be any faster, but you can do this as a self-join with 
subquery:

SELECT c1.from_id, c1.to_id, c1.val
FROM
  correlation c1,
  (
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
  ) AS c2
  (
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
  ) AS c3
WHERE
  c1.from_id = c2.to_id
  AND c1.to_id = c3.to_id
  AND c1.val > 

Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??

[Moran.Michael]

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 way below:
 
1. Pass into the function the decrypted string ''BCDE' 
2. The function looks up the ID that matchines the encrypted value of
'BCDE' ...
3. Finally: the matching ID of 2 is returned.
 
How would that change your solution below?

Thanks again in advance.
 
 
-Michael Moran
 

  _  

From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
Sent: Thu 3/24/2005 9:58 AM
To: Moran.Michael; Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto
= ??




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 PROTECTED], pgsql-sql@postgresql.org 
Sent: Thu, 24 Mar 2005 09:43:18 -0800 
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 

> 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 
> plpgsql Functions? 
> 
> Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it

> seems logical to try to match the declared return type)... it fails 
> 
> So, this works:return c; 
> This doesn't:  return c::VARCHAR; 
> 
> I always thought matching my return type to my funtion's RETURN
declaration 
> is logical... but now I know that if want a VARCHAR, I gotta manipulate it

> as a TEXT within my function when using PGCrypto. Any idea why? 
> 
> Thank again, Jim! 
> 
>   _ 
> 
> From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
 ] 
> Sent: Thu 3/24/2005 9:14 AM 
> To: Moran.Michael; pgsql-sql@postgresql.org 
> Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 
> 
> 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; 
> 
>RETURN c; 
> END; 
> ' LANGUAGE 'plpgsql'; 
> 
> -- Original Message --- 
> From: "Moran.Michael" <[EMAIL PROTECTED]> 
> To: pgsql-sql@postgresql.org 
> Sent: Thu, 24 Mar 2005 08:41:34 -0800 
> Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 
> 
> > 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 of data: 
> > 
> > INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD', 
> 'password', 
> > 'aes')); 
> > INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE', 
> 'password', 
> > 'aes')); 
> > INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF', 
> 'password', 
> > 'aes')); 
> > 
> > 3. Using the psql tool, selecting * from the crypto table yields the 
> > following: 
> > 
> > # select * from crypto; 
> >  id | title |crypted_content 
> > +---+ 
> >   1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215 
> >   2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017 
> >   3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266 
> > 
> > Pretty straight forward, right? 
> > 
> > Now how about doing this in a simple plpgsql Function. That's where we 
> > encounter problems. I want to get DECRYPTED data based on an input ID. 
> So... 
> > 
> > 4. Imagine the following simple plpgsql function (note I'm trying to 
> decrypt 
> > the table's encrypted BYTEA column into a decrypted VARCHAR for return):

> > 
> > CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER) 
> > RETURNS VARCHAR 
> > AS ' 
> > DECLARE 
> > crypto_cursor   CURSOR (input INTEGER) FOR SELECT 
> > encode(decrypt(crypted_content, decode(''password''::text, 
> > ''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE
id 
> = 
> > input; 
> > crypto  crypto.crypted_content%TYPE; 
> > tid ALIAS FOR $1; 
> > 
> > BEGIN 
> > OPEN crypto_cursor( tid ); 
> > LOOP 
> > FETCH crypto_cursor INTO crypto; 
> > EXIT WHEN NOT FOUND; 
> > END LOOP; 
> > CLOSE crypto_cursor; 
> > RETU

Re: [SQL] Self-referencing table question

[Sean Davis]

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 (i.e., reusing the query), though, then 
your solution is likely to be very useful.


Note that the subselect version takes about 10 times as long as the 
temptable version, and does not seem to be dependent on what data 
might be cached.

Nice.  Thanks for doing my work for me!  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 are cached.  As I understand it, the temp 
table will stick around, so I will have to be careful to explicitly 
drop it if I don't want it to persist?  Also each table will need a 
unique name (I have a session_id I can use), as it is possible that 
multiple temp tables will exist and be visible to each other?

Thanks again,
Sean
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Self-referencing table question

[Edmund Bacon]

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 are cached.  As I understand it, the temp 
table will stick around, so I will have to be careful to explicitly 
drop it if I don't want it to persist?  
I'm guessing so.However you could put everything in a transaction 
and use CREATE TEMP TABLE ... ON COMMIT DROP, and use INSERT INTO rather 
than SELECT INTO.  The speed should be about equivalent - but you'd have 
to test to make sure.

Also each table will need a unique name (I have a session_id I can 
use), as it is possible that multiple temp tables will exist and be 
visible to each other?
Each session (connection in your case?) has it's own temporary table 
space, so you shouldn't have to worry about that.

 

Thanks again,
Sean
--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??

[Moran.Michael]

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 the corresponding ID (INTEGER) value for the crypto data.
--
CREATE OR REPLACE FUNCTION selectFromCrypto2(VARCHAR)
RETURNS INTEGER
AS '
DECLARE
_crypt_data  ALIAS FOR $1;
ret INTEGER;
BEGIN
SELECT id INTO ret FROM crypto WHERE encode( decrypt(crypted_content,
decode(''password''::text, ''escape''::text), ''aes''::text),
''escape''::text ) = _crypt_data;
RETURN ( ret );
END;
' LANGUAGE 'plpgsql';

 
Thanks, all. This postgresql forum rocks!
Michael Moran
 

  _  

From: Moran.Michael
Sent: Thu 3/24/2005 10:59 AM
To: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??



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 way below: 
  
1. Pass into the function the decrypted string ''BCDE' 
2. The function looks up the ID that matchines the encrypted value of 
'BCDE' ... 
3. Finally: the matching ID of 2 is returned. 
  
How would that change your solution below? 

Thanks again in advance. 
  
  
-Michael Moran 
  

  _  

From: Jim Buttafuoco [mailto:[EMAIL PROTECTED] 
] 
Sent: Thu 3/24/2005 9:58 AM 
To: Moran.Michael; Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto 
= ?? 




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 PROTECTED], pgsql-sql@postgresql.org 
Sent: Thu, 24 Mar 2005 09:43:18 -0800 
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 

> 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 
> plpgsql Functions? 
> 
> Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it


> seems logical to try to match the declared return type)... it fails 
> 
> So, this works:return c; 
> This doesn't:  return c::VARCHAR; 
> 
> I always thought matching my return type to my funtion's RETURN 
declaration 
> is logical... but now I know that if want a VARCHAR, I gotta manipulate it


> as a TEXT within my function when using PGCrypto. Any idea why? 
> 
> Thank again, Jim! 
> 
>   _ 
> 
> From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
  
 > ] 
> Sent: Thu 3/24/2005 9:14 AM 
> To: Moran.Michael; pgsql-sql@postgresql.org 
> Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 
> 
> 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; 
> 
>RETURN c; 
> END; 
> ' LANGUAGE 'plpgsql'; 
> 
> -- Original Message --- 
> From: "Moran.Michael" <[EMAIL PROTECTED]> 
> To: pgsql-sql@postgresql.org 
> Sent: Thu, 24 Mar 2005 08:41:34 -0800 
> Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 
> 
> > 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 of data: 
> > 
> > INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD', 
> 'password', 
> > 'aes')); 
> > INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE', 
> 'password', 
> > 'aes')); 
> > INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF', 
> 'password', 
> > 'aes')); 
> > 
> > 3. Using the psql tool, selecting * from the crypto table yields the 
> > following: 
> > 
> > # select * from crypto; 
> >  id | title |crypted_content 
> > +---+ 
> >   1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215 
> >   2 | test2 | D\211\256E\372^Y\0259\341\215\2

Re: [SQL] Self-referencing table question

[Sean Davis]

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 code is running under 
mod_perl, so connections are cached.  As I understand it, the temp 
table will stick around, so I will have to be careful to explicitly 
drop it if I don't want it to persist?
I'm guessing so.However you could put everything in a transaction 
and use CREATE TEMP TABLE ... ON COMMIT DROP, and use INSERT INTO 
rather than SELECT INTO.  The speed should be about equivalent - but 
you'd have to test to make sure.

Also each table will need a unique name (I have a session_id I can 
use), as it is possible that multiple temp tables will exist and be 
visible to each other?
Each session (connection in your case?) has it's own temporary table 
space, so you shouldn't have to worry about that.

Sessions don't map 1-to-1 with connections in the web environment.  It 
is possible that a connection to the database would be simultaneously 
serving multiple users (sessions), if I understand Apache::DBI 
correctly.  In any case, this is probably a viable solution.

Sean
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

[SQL] Calling functions from Delete View (+ contrib/pgcrypto) = madness ??

[Moran.Michael]

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 | test2 | 
  3 | test3 | 
  4 | test4 | 
(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_dataALIAS 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, '');
 func_delfromcrypto

  1
(1 row)
 
 
Check the output (there is one less row now):
 
# select * from crypto_view;
 id | title | crypted_content
+---+-
  1 | test1 | 
  2 | test2 | 
  3 | test3 | 
(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:
s

Re: [SQL] Calling functions from Delete View (+ contrib/pgcrypto) = madness ??

[Jim Buttafuoco]

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 | test2 | 
>   3 | test3 | 
>   4 | test4 | 
> (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_dataALIAS 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, '');
>  func_delfromcrypto
> 
>   1
> (1 row)
> 
> Check the output (there is one less row now):
> 
> # select * from crypt