FreshPorts page logging

page_load_detail

freshports.dev=# \d page_load_detail
                                        Table "public.page_load_detail"
     Column     |          Type          | Collation | Nullable |                   Default                    
----------------+------------------------+-----------+----------+----------------------------------------------
 id             | integer                |           | not null | nextval('page_load_detail_id_seq'::regclass)
 date           | date                   |           | not null | 'now'::text::date
 time           | time without time zone |           | not null | 'now'::text::time(6) without time zone
 page_name      | text                   |           | not null | 
 user_id        | integer                |           |          | 
 ip_address     | inet                   |           | not null | 
 full_url       | text                   |           | not null | 
 rendering_time | interval               |           | not null | 
Indexes:
    "page_load_detail_test_pkey" PRIMARY KEY, btree (id)
    "page_load_detail_date" btree (date)
    "page_load_ip_address" btree (ip_address)

page_load_summary

freshports.dev=# \d page_load_summary
                                   Table "public.page_load_summary"
       Column       |   Type   | Collation | Nullable |                    Default                    
--------------------+----------+-----------+----------+-----------------------------------------------
 id                 | integer  |           | not null | nextval('page_load_summary_id_seq'::regclass)
 date               | date     |           | not null | 
 page_name          | text     |           | not null | 
 total              | integer  |           | not null | 
 users              | integer  |           | not null | 
 rendering_time_min | interval |           | not null | 
 rendering_time_max | interval |           | not null | 
 rendering_time_avg | interval |           | not null | 
Indexes:
    "page_load_summary_pkey" PRIMARY KEY, btree (id)
    "page_loads_date_date" UNIQUE, btree (date, page_name)

freshports.dev=# 

PageLoadSummaryUpdate

CREATE OR REPLACE FUNCTION PageLoadSummaryUpdate(date) returns int AS $$
   DECLARE
       l_RowCount   int;

   BEGIN
   INSERT INTO page_load_summary (date, page_name, total, users, rendering_time_min, rendering_time_max, rendering_time_avg)
      SELECT date,
             CASE WHEN page_name = '/missing.php' THEN
                CASE WHEN position('?' in full_url) != 0 THEN
                   substring(full_url from 0 for position('?' in full_url))
                ELSE
                    CASE WHEN position('cgi-bin/ads/' in full_url) = 0 THEN
                        full_url
                     ELSE
                        substring(full_url from 0 for position('%20' in full_url))
                     END
                END
                ELSE page_name
             END,
             count(*)            AS total,
             count(user_id)      AS users,
             min(rendering_time) AS min,
             max(rendering_time) AS max,
             avg(rendering_time) AS avg
        FROM page_load_detail
       WHERE date = $1
    GROUP BY 1, 2;

    GET DIAGNOSTICS l_RowCount = ROW_COUNT;

    return l_RowCount;

    END;
$$  LANGUAGE 'plpgsql';