CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresqldatabase-designtriggers
joncodo
joncodo

Reputation: 2328

Database Trigger on Column Query

What I want to do in the db is have MYFUNC()

I then want to send a query down to the db in the form of

select * from foo where col = 'my name'

and have the database handle it like:

select * from foo where MYFUNC(col) = MYFUNC('my name')

So I want my db functions to be hidden to the developer. I want them to match based on a matching algorithm but when the noobs look at it, they will say, 'Oh, Ill just match the name to the name and be done with it!'

Is there a way to hide this function so the developer does not need to remember to use the function each time?

This is in PostgresSQL 9.2 by the way.

Upvotes: 0

Views: 72

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

You can encapsulate it in a plain SQL function:

CREATE OR REPLACE FUNCTION f_get_foo(text)
  RETURNS SETOF foo AS
$func$
SELECT * FROM foo WHERE myfunc(col) = myfunc($1)
$func$
  LANGUAGE sql;

Then the call would be:

SELECT * FROM f_get_foo('my name');

Triggers are not directly applicable for this.
You could use rules on a table or view to bend things. But I am not going to assist with such a dubious approach.

Upvotes: 1

Related Questions