2

I am using the Chinook Database as a way to test a concept using encryption. I found that I could use the LIKE key word if I did:

SELECT pgp_sym_decrypt("Name", '22') FROM "Artist" WHERE pgp_sym_decrypt("Name", '22') LIKE '%a%';

I'm currently trying to avoid having to use the second pgp_sym_decrypt("Name", '22') if at all possible thanks.

Joe Jazdzewski
  • 723
  • 2
  • 7
  • 16

1 Answers1

1

Seems like you want to use a column alias. but that's not possible in the WHERE or HAVING clause, which only reference input columns, not output columns - as defined by the SQL standard. Details in this related answer:
GROUP BY + CASE statement

You would have to use a subquery as wrapper, which kind of defies your aim to shorten the code and may be slower if the optimizer fails to see through your stunt:

SELECT *
FROM  (SELECT pgp_sym_decrypt("Name", '22') AS expr FROM "Artist") sub
WHERE  expr LIKE '%a%';
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • @JJspacer: I suggest you open a new question with all the necessary details for [\[postgresql-performance\]](http://stackoverflow.com/tags/postgresql-performance/info). Be sure to search for related questions and answers first - there are quite a few here. Generally, searching through encrypted strings - especially passwords - is tricky. – Erwin Brandstetter May 23 '14 at 17:09