Обсуждение: Replace usage of a table in query with its array values

Hi everybody,

I have the following 3 tables:

sent_messages

 uid |   receiver        | theme    |             date
-----+----------------------+-------------+-------------------------------
   1 | +40741775630  | CAND1 | 2007-06-12 14:06:57.843024+00
   2 | +40741775630  | MAMA  | 2007-06-12 14:07:03.14247+00
   3 | +40741775630  | MAMA  | 2007-06-12 14:10:01.250825+00
   4 | +40741775621  | LIA       | 2007-07-02 07:02:28.245648+00
   5 | +40741775621  | LIA       | 2007-07-02 07:02:32.541576+00
   6 | +40741775621  | LIA       | 2007-07-02 07:02:33.80777+00
   7 | +40741775621  | LIA       | 2007-07-02 07:02:34.780668+00
   8 | +40741775621  | LIA       | 2007-07-02 07:02:35.664055+00
   9 | +40741775621  | LIA       | 2007-07-02 07:02:36.445283+00
  10 | +40741775621 | MIA      | 2007-07-02 07:02:41.518046+00
  11 | +40741775621 | MIA      | 2007-07-02 07:02:42.287176+00
  12 | +40741775621 | MIA      | 2007-07-02 07:02:43.003328+00
  13 | +40741775621 | MIA      | 2007-07-02 07:02:43.581017+00
  14 | +40741775621 | MIA      | 2007-07-02 07:02:44.163022+00
  15 | +40741775621 | MIA      | 2007-07-02 07:02:44.720369+00
  16 | +40741775621 | MIA      | 2007-07-02 07:02:45.346606+00
  17 | +40741775622 | MIA      | 2007-07-02 07:02:52.061171+00
  18 | +40741775622 | MIA      | 2007-07-02 07:02:52.85079+00
  19 | +40741775622 | MIA      | 2007-07-02 07:02:53.913305+00
  20 | +40741775622 | MIA      | 2007-07-02 07:02:54.50295+00
  21 | +40741775622 | MIA      | 2007-07-02 07:02:55.108986+00

themes
 uid | theme
-----+-------
   8 | MIA
   7 | LIA

reminder_services
 uid | theme_uid | activity_mt_amount | activity_min_days | activity_max_months | inactivity_days | limit_reminders | limit_months |         scanning_time
------+---------------+------------------------------+---------------------------+-------------------------------+-----------------------+-----------------------+-------------------+-------------------------------------------
  20 |             8 |                             4 |                         3 |                              2 |                     0 |                      3 |                 6 | 2007-07-02 07:38:04.349592+00                 (current_time)
  34 |             7 |                             7 |                         1 |                              1 |                     0 |                      1 |                 1 | 2007-07-02 07:38:04.349592+00                 (current_time)



I created the following query using this three tables:

SELECT DISTINCT filtered.theme,
                filtered.receiver
  FROM ( SELECT SUM( B.count ),
                A.theme,
                A.receiver,
                A.dates,
                A.activity_min_days,
                A.activity_MT_amount,
                A.activity_max_months
           FROM ( SELECT DISTINCT sent_messages.theme,
                         reminder_services.theme_uid,    
                         receiver,
                         ARRAY( SELECT date::date + s.a FROM generate_series( 0, activity_min_days -1 ) AS s( a ) ) AS dates,
                         activity_min_days,
                         activity_MT_amount,
                         activity_max_months
                    FROM reminder_services,
                         themes,
                         sent_messages
                   WHERE themes.uid = reminder_services.theme_uid
                     AND sent_messages.theme = themes.theme
                     AND date_trunc( 'day', sent_messages.date ) > ( now() - reminder_services.activity_max_months * INTERVAL'1 month' )
                     AND date_trunc( 'day', sent_messages.date ) < ( now() - reminder_services.inactivity_days * INTERVAL'1 day')
                     AND EXTRACT( DOW FROM CURRENT_TIMESTAMP )    = EXTRACT( DOW FROM scanning_time)  
                     AND EXTRACT( HOUR FROM CURRENT_TIMESTAMP )   = EXTRACT( HOUR FROM scanning_time )
                     AND EXTRACT( MINUTE FROM CURRENT_TIMESTAMP ) = EXTRACT( MINUTE FROM scanning_time ) ) A
     INNER JOIN ( SELECT COUNT(*),
                         sent_messages.theme,
                         receiver,
                         date_trunc( 'day', sent_messages.date ) AS date
                    FROM reminder_services,
                         themes,
                         sent_messages
                   WHERE themes.uid = reminder_services.theme_uid
                     AND sent_messages.theme = themes.theme
                     AND date_trunc( 'day', sent_messages.date ) > ( now() - reminder_services.activity_max_months * INTERVAL' 1 month' )
                     AND date_trunc( 'day', sent_messages.date ) < ( now() - reminder_services.inactivity_days * INTERVAL'1 day' )
                     AND EXTRACT( DOW FROM CURRENT_TIMESTAMP )    = EXTRACT( DOW FROM scanning_time)  
                     AND EXTRACT( HOUR FROM CURRENT_TIMESTAMP )   = EXTRACT( HOUR FROM scanning_time )
                     AND EXTRACT( MINUTE FROM CURRENT_TIMESTAMP ) = EXTRACT( MINUTE FROM scanning_time )
                GROUP BY sent_messages.theme, receiver, date ) B
             ON A.theme = B.theme
            AND A.receiver = B.receiver
            AND B.date = ANY( A.dates )
       GROUP BY A.theme,
                A.receiver,
                A.dates,
                A.activity_min_days,
                A.activity_MT_amount,
                A.activity_max_months) AS filtered
 WHERE filtered.sum >= filtered.activity_MT_amount;

The above query returns the following result:

theme |   receiver
---------+--------------
 MIA   | +40741775621
 MIA   | +40741775622

After database redesign it was decided that reminder_services table to be moved
into another database. In production, reminder_services table will never have more
than 100 of records, but sent_messages table it is a log table with a lot of records
and the above query will be executed every minute.  So, I was thinking to keep
the skeleton of the above query and using arrays instead of using the reminder_services
table. I want that the following query to be executed  and then the arrays to be used in
the above query:

SELECT ARRAY( SELECT uid FROM reminder_services ) AS uid,
        ARRAY( SELECT theme_uid FROM reminder_services) as theme_uid,
        ARRAY( SELECT activity_mt_amount FROM reminder_services) as activity_mt_amount,
        ARRAY( SELECT activity_min_days FROM reminder_services) as activity_min_days,
        ARRAY( SELECT activity_max_months FROM reminder_services) as activity_max_months,
        ARRAY( SELECT inactivity_days FROM reminder_services) as inactivity_days,
        ARRAY( SELECT limit_reminders FROM reminder_services) as limit_reminders,
        ARRAY( SELECT limit_months FROM reminder_services) as limit_months,
        ARRAY( SELECT scanning_time FROM reminder_services) as scanning_time;
 
My problem is that I don't know how to integrate(loop, access etc) these arrays in the query.
Hope it's possible. Any other ideea would be greatly appreciated.


Best,
    Loredana

Loredana Curugiu wrote:
> After database redesign it was decided that reminder_services table
> to be moved into another database. In production, reminder_services
> table will never have more than 100 of records, but sent_messages
> table it is a log table with a lot of records and the above query
> will be executed every minute.

Cross posting to multiple lists is frowned upon, so I'm reply only here.
  Have you considered using the dblink contrib module?  That seems a far
simpler solution than trying to do this with arrays.

--
Guy Rouillier