Call plpgsql function in select's where clause

1
votes

I have a plpgsql function complementwhere(parameter1 date) which returns a complex where clause as a text, let's say it returns clause2 = filter2 just as an example, but it actually returns a bunch of other clauses.

I need to call this function in a select's where clause to complement that clause, this is what I have so far:

SELECT value1 FROM table1 WHERE clause1 = filter1 AND complementwhere(parameter1);

But this returns the error

argument of WHERE must be type boolean, not type text

What is the right way to do this?

1
postgresqlplpgsqldynamic-sql
You can try to pass whole row to your function and change it so it will return boolean that indicates if row matches your complex where clause. See this java2s.com/Code/PostgreSQL/Store-Procedure-Function/… – Konstantin V. Salikhov

1 Answers

1
votes

You are trying to convert a text value into code, which is not normally possible in SQL. You need to either do two round trips to the server.

  1. Fetch WHERE clause: SELECT complementwhere('<mydate>').
  2. Build the query based on the first and execute it: SELECT .. WHERE ....

Or (preferably) use dynamic SQL with EXECUTE in a single PL/pgSQL function. There are many examples here on SO, try a search.

You didn't provide your actual code ...

Be wary of SQL injection whenever you turn user input into code.