Break PL/pgSQL functions into smaller parts

Question

I would like to write an API function that is modular. The problem is, modular parts feature same queries. I have suspicion, that same queries in every subfunction will affect perfomance.

Example:

    MAIN_FUNCTION: base_data(customer_id, user_id) {
      SELECT *
        FROM get_absence(customer_id, user_id)
        JOIN get_work_hours(customer_id, user_id)
       USING (worker_id)
    }

    get_absence(customer_id, user_id) {
      RETURN QUERY
      SELECT *
        FROM get_user_workers(customer_id, user_id)
        JOIN absence_table
       USING (worker_id)
    }

    get_work_hours(customer_id, user_id) {
      RETURN QUERY
      SELECT *
        FROM get_user_workers(customer_id, user_id)
        JOIN workhours_table
       USING (worker_id)
    }

Sorry for the pseudocode, but it is a bit shorter. Basically, most of the subfunctions would contain the get_user_workers query. If I would build a big and boring function, I would query workers with common table expression.

How to achieve readability and modularity, instead of countless lines of queries?

***Always*** include a complete function header (and footer) with name, parameters, data types, return type, language declaration, ... And of course, your version of Postgres. — Erwin Brandstetter, Oct 21 '14 at 12:07
Is your last sentence really your question? The rest of your post makes it sound like you're really asking, "how do I avoid calling get_user_workers() multiple times"? — Dan Getz, Oct 21 '14 at 12:32
This style is most worst from performance perspective. You can very effective block a query optimizer. SQL doesn't support this small granularity. If you need it, use a views instead trivial one line sql query functions. — Pavel Stehule, Oct 22 '14 at 06:31
I see no point in adding complete header and footer, as the problem is not with the small tweaks, but with method in general. And no, the question is not only how do I avoid calling functionX N-times, but also how do I get an elegant and maintainable code. — user1190411, Oct 22 '14 at 09:23

score 2 · Accepted Answer · edited May 23 '17 at 12:09

2

You can do that easily:

CREATE FUNCTION get_absence(...)  RETURNS TABLE (...) ...;
CREATE FUNCTION get_work_hours(...) RETURNS TABLE (...) ...;

CREATE FUNCTION base_data(_customer_id int, _user_id int)
  RETURNS TABLE (...) AS
$func$
   SELECT a.*, w.*
   FROM   get_absence(_customer_id, _user_id) a
   JOIN   get_work_hours(_customer_id, _user_id) w ON w.??? = a.???;
$func$ LANGUAGE sql;

Using an SQL function for the simple example. The same applies for PL/pgSQL because the issue is a matter of SQL, really.

In the FROM clause, a set-returning function can be used just like a table. But call set-returning functions with:

SELECT * FROM base_data(1,2);

Be aware that simple SQL functions can be "inlined", while PL/pgSQL functions are always executed separately (pose as optimization barrier) - but their execution plan is saved and reused in the same session (if Postgres finds that to be cheaper). Each has their pros and cons.

Typically, a single, integrated and optimized query without sub-functions is the fastest. May hardly matter or make a relevant difference, depending on many circumstances.

Details:

edited May 23 '17 at 12:09

Community

1
1

answered Oct 21 '14 at 12:20

Erwin Brandstetter

605,456
145
1,078
1,228

1

I am thinking so this is just wrong strategy - this should be done with views, not with functions. You try to merge functional world and relational world. It should work with smaller project, where query optimization is not important, but for large databases this strategy is simply wrong - a functional API have to wrap a complete optimized queries. – Pavel Stehule Oct 22 '14 at 06:35
Ok, the function thingy seems not very clean. base_data should then contain all the functions, that are going to be used, I cannot hide some functions in lower levels. Thanks Pavel, I'll try to use the tao of view. – user1190411 Oct 22 '14 at 09:38
The only thing, that buggers me with views, is that I cannot use generate_series in them. That means big chunks of SQL inside the main function, surrounded partialy by views. – user1190411 Oct 22 '14 at 11:37
http://stackoverflow.com/a/25256211/1190411 Seems I can use session variables with views. Thank you all. – user1190411 Oct 22 '14 at 13:39
@user1190411: That's a misunderstanding. Of course you can use `generate_series()` in a function. Also, a simple, `IMMUTABLE` sql function (just `SELECT 1234` for example) is just fine to provide the equivalent of a global variable. You might post a new question with the (simplified) whole picture to get an optimal solution. – Erwin Brandstetter Oct 22 '14 at 14:48

Break PL/pgSQL functions into smaller parts

1 Answers1