Set-returning functions are not allowed in where

by

Set-returning functions refer to PostgreSQL functions that return a set of rows. These functions can be used in various parts of a query, such as SELECT, FROM, or HAVING clauses. However, they are not allowed in the WHERE clause of a query.

The WHERE clause is used to filter rows based on a condition. It expects a Boolean expression that evaluates to either true or false. Since set-returning functions can potentially return multiple rows, using them directly in the WHERE clause would result in ambiguity regarding which rows should be filtered.

To illustrate this, let’s consider an example where we have a table named “employees” with columns such as “employee_id”, “name”, and “salary”. In this scenario, we cannot directly use a set-returning function in the WHERE clause. For instance, let’s assume there is a function “get_high_paid_employees()” that returns a set of employee IDs who earn a high salary. 

    
    SELECT employee_id, name, salary
    FROM employees
    WHERE employee_id IN (get_high_paid_employees()); -- Invalid usage

The above query is invalid because “get_high_paid_employees()” is a set-returning function used in the WHERE clause. Instead, we should use set-returning functions in the SELECT or FROM clauses or subqueries. For example, we can modify the query to achieve the desired result: 

    
    SELECT employee_id, name, salary
    FROM employees
    WHERE employee_id IN (
      SELECT employee_id
      FROM get_high_paid_employees() -- Using set-returning function in a subquery
    );

In the revised query, the set-returning function “get_high_paid_employees()” is used in a subquery to retrieve the set of employee IDs. Then, the WHERE clause filters the rows where the employee ID is present in the result of the subquery.

Read more

Leave a comment