PostgreSQL. Stored function or query improvements

Question

I'm using PostgreSQL and have an employee table:

employeeid | FirstName | LastName | Department | Position
    1      | Aaaa      |          |            |
    2      | Bbbb      |          |            |
    3      |           |          |            |
    .      |           |          |            |

Reports table:

employeeid |        enter        |        exit
     1     | 2020-11-08 09:02:21 | 2020-11-08 18:12:01
     .     |         ...         |

Now, I'm querying for missed employees on a certain date something like that function:

for i in select employeeid from employee
loop
    if select not exists(select 1 from reports where enter::date = '2020-11-08' and employeeid = i) then
       return query select employeeid, lastname, firstname, department, position from employee where employeeid = i;        
    end if;
end loop;

Seems to me, it's not an ideal solution. Is there any better approach to achieve the same result? Thanks.

If you have a new question, then ask a new question (possibly linking back to this one). Don't extend your existing one — user330315
– user330315, Commented Nov 9, 2020 at 7:16

Pavel Stehule · Accepted Answer · 2020-11-09 06:28:39Z

2

Your code is pretty non effective. Probably it cannot be written badly :-). The reply @a_horse_with_no_name is absolutely correct, just for completeness I'll fix your plpgsql code.

DECLARE _e employee;
BEGIN
  FOR _e IN SELECT * FROM employee
  LOOP
    IF NOT EXISTS(SELECT * 
                    FROM reports r
                   WHERE r.enter::date = date '2020-11-08'
                     AND r.employeeid = _e.employeeid)
    THEN
      RETURN NEXT _e;
    END IF;
  END LOOP;
END;

When you write stored procedure (or any query based application), the important value is number of queries - low number is better (attention - there are exceptions - sometimes too much complex queries can be slower due their complexity). In your example there are employees * 2 + 1 queries (with larger overhead of plpgsql - RETURN QUERY is more expensive than RETURN NEXT). Solution proposed by @a_horse_with_no_name is one query (without a overhead of plpgsql). Any my example has employees + 1 queries (with lower overhead of plpgsql).

Your example is good example of one common SQL antipattern - "using ISAM style".

edited Nov 9, 2020 at 6:28

answered Nov 8, 2020 at 20:01

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

6 Comments

Ma'ruf Over a year ago

Thanks for the deep explanation. One more question: how about if I need results in the period of the time? For example From=2020-11-0, To=2020-11-08. And result employeeid, lastname, firstname, department, position, misseddate

Pavel Stehule Over a year ago

@Ma'ruf - it is not a problem just you can use BETWEEN operator instead =. When the result of query has not same structure like your expected result, then you can use a custom composite type. Almost all is possible - plpgsql and SQL are mature languages. Please, start by plpgsql documentation reading - it has less 50 pages, and you will see all possibilities - postgresql.org/docs/current/plpgsql.html

Ma'ruf Over a year ago

how about misseddate column in the return table? Now I'm updating my question.

Pavel Stehule Over a year ago

The identifiers in RETURNS TABLE(...) are OUT variables too. So you can fill these variables and use RETURN NEXT without arguments, or you can use composite value in REURN NEXT like RETURN NEXT (r.employeeid, r. ..., _f_from_date).

Ma'ruf Over a year ago

so is that the correct way of looping through date ranges? _res = f_to - f_from; for i in 0.._res loop _from = f_from + i;

|

user330315 · Accepted Answer · 2020-11-08 17:43:06Z

0

You are right: more often than not, a loop is a bad way to do things in a relational database.

You can do this in plain SQL:

select e.employeeid, e.lastname, e.firstname, e.department, e.position 
from employee e
where not exists (select *
                  from reports r
                  where r.enter::date = date '2020-11-08'
                    and e.employeeid = r.employeeid);

This will return all employees for which no row exists in the reports table on 2020-11-08

answered Nov 8, 2020 at 17:43

user330315

Collectives™ on Stack Overflow

PostgreSQL. Stored function or query improvements

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related