CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sql
Edson Horacio Junior
Edson Horacio Junior

Reputation: 3143

Call plpgsql function in select's where clause

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?

Upvotes: 1

Views: 883

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658482

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.

Upvotes: 1

Related Questions