Database Trigger on Column Query

Question

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.

Be careful about performance implications of putting a column in a black box. Assuming col is indexed, the first query is index seek, but the second is full table scan. — Branko Dimitrijevic
– Branko Dimitrijevic, Commented Jan 4, 2014 at 14:50

Erwin Brandstetter · Accepted Answer · 2014-01-03 20:04:01Z

1

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.

answered Jan 3, 2014 at 20:04

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joncodo Over a year ago

I think upon further discussion with my boss, we will abandon this idea. Thanks for the answer though. If I were to do it a function would be the way to go.

Collectives™ on Stack Overflow

Database Trigger on Column Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related