I would like to know if there is a way to move arguments from the WHERE clause into a function call:
For example:
SELECT * FROM table1
WHERE a1 = ‘abc’ AND a2 = ‘def’;
But I would like the query to be rewritten as something like:
SELECT * FROM func1('a1=abc','b1=def');
It doesn’t matter what object table1 is, but I would imagine it could be a VIEW. In any case, I would like to provide an interface to the users like shown above, whereby the function-call gets hidden.
You can create a set returning function that you can call like this:
SELECT *
FROM func1('abc', 'def');
The function looks something like this:
create or replace function func1(text, text)
returns setof table1
as
$body$
SELECT * FROM table1 WHERE a1 = $1 and a2 = $2
$body$
LANGUAGE SQL;
More details in the manual:
http://www.postgresql.org/docs/current/static/xfunc-sql.html
You can also append a where clause to the select:
SELECT *
FROM func1('abc', 'def')
WHERE some_col > 100;