Call plpgsql function in select's where clause

Question

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?

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
– Konstantin V. Salikhov, Commented Aug 1, 2014 at 13:36

Erwin Brandstetter · Accepted Answer · 2021-08-15 00:13:16Z

1

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.

Fetch WHERE clause: SELECT complementwhere('<mydate>').
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.

edited Aug 15, 2021 at 0:13

answered Aug 1, 2014 at 13:35

Erwin Brandstetter

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

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

Collectives™ on Stack Overflow

Call plpgsql function in select's where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related