Learning PostgreSQL 11 - Third Edition

Executing DML statements in dynamic SQL

Some applications might interact with data in an interactive manner. For example, we might have billing data generated on a monthly basis. Also, some applications filter data on different criteria, as defined by the user. In such cases, dynamic SQL is very convenient. For example, in the car portal application, the search functionality is needed to get accounts using the dynamic predicate, as follows:

CREATE OR REPLACE FUNCTION car_portal_app.get_account (predicate TEXT) RETURNS SETOF car_portal_app.account AS$$BEGIN  RETURN QUERY EXECUTE 'SELECT * FROM car_portal_app.account WHERE ' || predicate;END;$$ LANGUAGE plpgsql;

We can test how the function can be used to evaluate several predicates, as follows: ...