Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 1FB929FB9CB for ; Mon, 2 Jul 2007 05:24:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 67361-06 for ; Mon, 2 Jul 2007 05:23:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.245]) by postgresql.org (Postfix) with ESMTP id 262869FB90A for ; Mon, 2 Jul 2007 05:23:59 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so291473ana for ; Mon, 02 Jul 2007 01:23:58 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=Il3L26MbCJ2G4lMQLD1OE31NCN6Wmm/Dgk5jBAxm6R7NEVtNvKhGmhqYwlaAGebD6RHVzN9l0p//CUShrJHmINv9A/PxD5fkDoOmUo7l8Ivb43XQsHrSjx6WJ/5VbQhNEw3Q+IE8LLb0Z/xS3GoCHBG2LMyR+YlyYdmIWZyoMfk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=m+8G8V7dIEdr0TBtOQEzGKUEY+FtSlN3BxtArtx3Hx0PGjdL5wdFTDJHCDqI5AxW2uFviGXroEKtHIhb2UrgPJ9m/q1VckRHLCCcezJsheOMM0jT4892HNMBfNtbQymQiNFjeyirvIr9miocrleIG1HKqDCHpjwGPolGZq6hkFc= Received: by 10.100.3.20 with SMTP id 20mr3529953anc.1183364638179; Mon, 02 Jul 2007 01:23:58 -0700 (PDT) Received: by 10.100.194.7 with HTTP; Mon, 2 Jul 2007 01:23:58 -0700 (PDT) Message-ID: <1c23c8e70707020123o5a8b6512i2d2ad8888cb921cc@mail.gmail.com> Date: Mon, 2 Jul 2007 11:23:58 +0300 From: "Loredana Curugiu" To: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org, pgsql-jdbc@postgresql.org Subject: Replace usage of a table in query with its array values MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_100401_382209.1183364638147" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/1 X-Sequence-Number: 17175 ------=_Part_100401_382209.1183364638147 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_100401_382209.1183364638147 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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



























------=_Part_100401_382209.1183364638147--