Hi 
i have an ordered table of dates let's say:

1/1/2004
8/1/2004
15/1/2004
29/1/2004
5/2/2004
12/2/2004

I am searching for a way to have the minimum date and maximum date for dates seperated by one week whitout gaps between them in a string.
which will give the following output:
1/1/2004:15/1/2004;29/1/2004:12/2/2004;

I was thinking of doing this with an aggregate function.

So i thought about writing the following C code :

#include "postgres.h"
#include "utils/date.h"

int32 i;//initially equal to zero
text * charresult;
/*
text * concat(,){} // NOT IMPLEMENTED (HOW TO DO IT ??)
*/

PG_FUNCTION_INFO_V1(computechar);

Datum  computechar(PG_FUNCTION_ARGS) {

           DateADT d1=PG_GETARG_DATEADT(0);
           DateADT d2=PG_GETARG_DATEADT(1);

            int32 diff= (int32) (d2 - d1);
            i++;

              if(diff == 7*i)
                PG_RETURN_DATEADT(d1);
              else
                {
               charresult=concat(charresult,d1);
               charresult=concat(charresult,":");
               charresult=concat(charresult,d2);
               charresult=concat(charresult,";");
               PG_RETURN_DATEADT(d2); 
                }
}


PG_FUNCTION_INFO_V1(returncomputedchar);

    Datum returncomputedchar (PG_FUNCTION_ARGS) {
                                        
                                           PG_RETURN_TEXT_P(charresult);
                                        }


And then i will create the aggregate as follows (after compiling ...) :

CREATE OR REPLACE FUNCTION computechar(date,date) returns date as '/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);
CREATE OR REPLACE FUNCTION returncomputedchar (date) returns text as '/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);

CREATE AGGREGATE groupe_weeks_agg( basetype = date, sfunc = computechar,stype = date, finalfunc = returncomputedchar); 


The function 'computechar' will put the result in the variable 'charresult' that will be returned by the function 'returncomputedchar'.

My first question is : Could this work the way i am thinking of it ??

My second question is how to implement the function 'concat' that will return a text concatenation of  dates and text.
(the question is basically how to transform a date into text).

It's the first time i try to implement an aggregate function and i need help + i haven't been using C language frequently the last few years (there may be errors) .

Thx.











Hi 
i have an ordered table of dates let's 
say:
 
1/1/2004
8/1/2004
15/1/2004
29/1/2004
5/2/2004
12/2/2004
 
I am searching for a way to have the minimum date 
and maximum date for dates seperated by one week whitout gaps between them 
in a string.
which will give the 
following output:
1/1/2004:15/1/2004;29/1/2004:12/2/2004;
 
I was thinking of doing this with an aggregate function.
 
So i thought about writing the following C code :
 
#include "postgres.h"#include "utils/date.h"
 
int32 i;//initially equal to zerotext * charresult;
/*text * concat(,){} // NOT IMPLEMENTED (HOW TO DO IT ??)*/
 
PG_FUNCTION_INFO_V1(computechar);
 
Datum  computechar(PG_FUNCTION_ARGS) {
 
           DateADT 
d1=PG_GETARG_DATEADT(0);    
       DateADT d2=PG_GETARG_DATEADT(1);
 
            int32 diff= 
(int32) (d2 - 
d1);            
i++;
 
              
if(diff == 7*i)       
         
PG_RETURN_DATEADT(d1);       
       
else            
    {            
   charresult=concat(charresult,d1);    
        
   charresult=concat(charresult,":");    
        
   charresult=concat(charresult,d2);               charresult=concat(charresult,";");               PG_RETURN_DATEADT(d2);     
            }}
 
PG_FUNCTION_INFO_V1(returncomputedchar);
 
    Datum returncomputedchar (PG_FUNCTION_ARGS) {
                                        

                                           PG_RETURN_TEXT_P(charresult);                                        }
 
And then i will create the aggregate as follows (after compiling 
...) :
 
CREATE OR REPLACE FUNCTION computechar(date,date) returns date as 
'/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);
CREATE OR REPLACE FUNCTION returncomputedchar (date) returns text as 
'/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);
 
CREATE AGGREGATE groupe_weeks_agg( basetype = date, sfunc = 
computechar,stype = date, finalfunc = returncomputedchar); 
 
 
The function 'computechar' will put the result in the variable 'charresult' 
that will be returned by the function 'returncomputedchar'.
 
My first question is : Could this work the way i am thinking of it 
??
 

My second question is how to implement the function 'concat' that will 
return a text concatenation of  dates and text.
(the question is basically how to transform a date into text).
 
It's the first time i try to implement an aggregate function and i need 
help + i haven't been using C language frequently the last few years 
(there may be errors) .
 
Thx.
Mon, 5 Jul 2004 12:23:28 +0200
"Najib Abi Fadel" <nabifadel@usj.edu.lb>
creating a complex aggregate function
Najib Abi Fadel wrote:
> Hi
> i have an ordered table of dates let's say:

No you don't (tables aren't ordered), and I think that's what's going to 
cause you trouble.

> 1/1/2004
> 8/1/2004
> 15/1/2004
> 29/1/2004
> 5/2/2004
> 12/2/2004
>  
> I am searching for a way to have the minimum date and maximum date for 
> dates seperated by one week whitout gaps between them in a string.
> which will give the following output:
> 1/1/2004:15/1/2004;29/1/2004:12/2/2004;
>  
> I was thinking of doing this with an aggregate function.
>  
> So i thought about writing the following C code :

My C is even rustier than yours, but you're assuming here that the dates 
you receive will be passed to you in order. I don't think PG guarantees 
this (perhaps with the exception of an explicit sort in a subselect). 
That's not to say it won't work when you test it, just that the order 
isn't guaranteed so you can't rely on it.

Now, for sum()/min() etc this doesn't matter, you only need to compare 
the current value with a "running total", but in your case you'll need 
to match against many different groups.

I think what you want here is a set-returning function, doing something 
like:

last_date := null;
FOR myrow IN SELECT id,tgt_date FROM my_dates ORDER BY tgt_date LOOP
   diff := myrow.tgt_date - last_date;
   IF diff = 7 THEN
     last_date:=myrow.tgt_date;
   ELSE
     -- Assemble a result row and return it here
   END IF;
END LOOP

The above is (roughly) plpgsql syntax.

> text * concat(,){} // NOT IMPLEMENTED (HOW TO DO IT ??)

The function you want is textcat(text,text). I believe all of the 
operators (|| + - etc) have equivalent functions. They're not listed in 
the user documentation, but "\df text*" will show you them.

HTH
-- 
   Richard Huxton
   Archonet Ltd

Mon, 05 Jul 2004 13:16:23 +0100
Richard Huxton <dev@archonet.com>
Re: creating a complex aggregate function
I forgot to mention that in fact i will have an ordered table of dates ( i mean by ordered i will do an order by before applying the aggregate) for different users.
That's why i need an aggregate ( i have to do a group by)

so i have something like this 


01/01/2004    user1
08/01/2004    user1
15/01/2004    user1
29/01/2004    user1
05/02/2004    user1
12/02/2004    user1

25/12/2003    user2
01/01/2004    user2
15/01/2004    user2
22/01/2004    user2
29/01/2004    user2
05/02/2004    user2
12/02/2004    user2

which should produce the output:

01/01/2004:15/01/2004;29/01/2004:12/02/2004;    user1
25/12/2003:01/01/2004;15/01/2004:12/02/2004;    user2

I hope someone will help me in this issue.
  ----- Original Message ----- 
  From: Najib Abi Fadel 
  To: generalpost 
  Cc: developPost 
  Sent: Monday, July 05, 2004 12:23 PM
  Subject: [GENERAL] creating a complex aggregate function


  Hi 
  i have an ordered table of dates let's say:

  1/1/2004
  8/1/2004
  15/1/2004
  29/1/2004
  5/2/2004
  12/2/2004

  I am searching for a way to have the minimum date and maximum date for dates seperated by one week whitout gaps between them in a string.
  which will give the following output:
  1/1/2004:15/1/2004;29/1/2004:12/2/2004;

  I was thinking of doing this with an aggregate function.

  So i thought about writing the following C code :

  #include "postgres.h"
  #include "utils/date.h"

  int32 i;//initially equal to zero
  text * charresult;
  /*
  text * concat(,){} // NOT IMPLEMENTED (HOW TO DO IT ??)
  */

  PG_FUNCTION_INFO_V1(computechar);

  Datum  computechar(PG_FUNCTION_ARGS) {

             DateADT d1=PG_GETARG_DATEADT(0);
             DateADT d2=PG_GETARG_DATEADT(1);

              int32 diff= (int32) (d2 - d1);
              i++;

                if(diff == 7*i)
                  PG_RETURN_DATEADT(d1);
                else
                  {
                 charresult=concat(charresult,d1);
                 charresult=concat(charresult,":");
                 charresult=concat(charresult,d2);
                 charresult=concat(charresult,";");
                 PG_RETURN_DATEADT(d2); 
                  }
  }


  PG_FUNCTION_INFO_V1(returncomputedchar);

      Datum returncomputedchar (PG_FUNCTION_ARGS) {
                                          
                                             PG_RETURN_TEXT_P(charresult);
                                          }


  And then i will create the aggregate as follows (after compiling ...) :

  CREATE OR REPLACE FUNCTION computechar(date,date) returns date as '/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);
  CREATE OR REPLACE FUNCTION returncomputedchar (date) returns text as '/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);

  CREATE AGGREGATE groupe_weeks_agg( basetype = date, sfunc = computechar,stype = date, finalfunc = returncomputedchar); 


  The function 'computechar' will put the result in the variable 'charresult' that will be returned by the function 'returncomputedchar'.

  My first question is : Could this work the way i am thinking of it ??

  My second question is how to implement the function 'concat' that will return a text concatenation of  dates and text.
  (the question is basically how to transform a date into text).

  It's the first time i try to implement an aggregate function and i need help + i haven't been using C language frequently the last few years (there may be errors) .

  Thx.










I forgot to mention that in fact i will have an 
ordered table of dates ( i mean by ordered i will do an order by before applying 
the aggregate) for different users.
That's why i need an aggregate ( i have to do a 
group by)
 
so i have something like this 
 
 

01/01/2004    user1
08/01/2004    user1
15/01/2004    user1
29/01/2004    user1
05/02/2004    user1
12/02/2004    user1
 
25/12/2003    user2

01/01/2004    user2
15/01/2004    user2
22/01/2004    user2
29/01/2004    user2
05/02/2004    user2
12/02/2004    
user2
 
which should produce the output:
 

01/01/2004:15/01/2004;29/01/2004:12/02/2004;    
user1
25/12/2003:01/01/2004;15/01/2004:12/02/2004;    
user2
 
I hope someone will help me in this 
issue.

----- Original Message ----- 
From:
Najib Abi 
  Fadel 
To: generalpost 
Cc: developPost 
Sent: Monday, July 05, 2004 12:23 
PM
Subject: [GENERAL] creating a complex 
  aggregate function


Hi 
i have an ordered table of dates let's 
  say:
 
1/1/2004
8/1/2004
15/1/2004
29/1/2004
5/2/2004
12/2/2004
 
I am searching for a way to have the minimum date 
  and maximum date for dates seperated by one week whitout gaps between 
  them in a string.
which will give the 
  following output:
1/1/2004:15/1/2004;29/1/2004:12/2/2004;
 
I was thinking of doing this with an aggregate function.
 
So i thought about writing the following C code :
 
#include "postgres.h"#include "utils/date.h"
 
int32 i;//initially equal to zerotext * charresult;
/*text * concat(,){} // NOT IMPLEMENTED (HOW TO DO IT ??)*/
 
PG_FUNCTION_INFO_V1(computechar);
 
Datum  computechar(PG_FUNCTION_ARGS) {
 
           DateADT 
  d1=PG_GETARG_DATEADT(0);    
         DateADT d2=PG_GETARG_DATEADT(1);
 
            int32 diff= 
  (int32) (d2 - 
  d1);            
  i++;
 
              
  if(diff == 7*i)       
           
  PG_RETURN_DATEADT(d1);       
         
  else            
      {        
      
     charresult=concat(charresult,d1);    
          
     charresult=concat(charresult,":");    
          
     charresult=concat(charresult,d2);               charresult=concat(charresult,";");               PG_RETURN_DATEADT(d2);     
              }}
 
PG_FUNCTION_INFO_V1(returncomputedchar);
 
    Datum returncomputedchar (PG_FUNCTION_ARGS) {
                                        

                                             PG_RETURN_TEXT_P(charresult);                                        }
 
And then i will create the aggregate as follows (after compiling 
  ...) :
 
CREATE OR REPLACE FUNCTION computechar(date,date) returns date as 
  '/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);
CREATE OR REPLACE FUNCTION returncomputedchar (date) returns text as 
  '/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);
 
CREATE AGGREGATE groupe_weeks_agg( basetype = date, sfunc = 
  computechar,stype = date, finalfunc = returncomputedchar); 
 
 
The function 'computechar' will put the result in the variable 
  'charresult' that will be returned by the function 'returncomputedchar'.
 
My first question is : Could this work the way i am thinking of it 
  ??
 

My second question is how to implement the function 'concat' that 
  will return a text concatenation of  dates and text.
(the question is basically how to transform a date into 
text).
 
It's the first time i try to implement an aggregate function and i need 
  help + i haven't been using C language frequently the last few years 
  (there may be errors) .
 
Thx.
Tue, 6 Jul 2004 12:23:27 +0200
"Najib Abi Fadel" <nabifadel@usj.edu.lb>
Re: creating a complex aggregate function