473,699 Members | 2,711 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_INF O_V1(computecha r);

Datum computechar(PG_ FUNCTION_ARGS) {

DateADT d1=PG_GETARG_DA TEADT(0);
DateADT d2=PG_GETARG_DA TEADT(1);

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

if(diff == 7*i)
PG_RETURN_DATEA DT(d1);
else
{
charresult=conc at(charresult,d 1);
charresult=conc at(charresult," :");
charresult=conc at(charresult,d 2);
charresult=conc at(charresult," ;");
PG_RETURN_DATEA DT(d2);
}
}
PG_FUNCTION_INF O_V1(returncomp utedchar);

Datum returncomputedc har (PG_FUNCTION_AR GS) {

PG_RETURN_TEXT_ P(charresult);
}
And then i will create the aggregate as follows (after compiling ...) :

CREATE OR REPLACE FUNCTION computechar(dat e,date) returns date as '/home/nabifadel/tempo/groupingWeeks.s o' LANGUAGE 'C' WITH (isStrict);
CREATE OR REPLACE FUNCTION returncomputedc har (date) returns text as '/home/nabifadel/tempo/groupingWeeks.s o' LANGUAGE 'C' WITH (isStrict);

CREATE AGGREGATE groupe_weeks_ag g( basetype = date, sfunc = computechar,sty pe = date, finalfunc = returncomputedc har);
The function 'computechar' will put the result in the variable 'charresult'tha t will be returned by the function 'returncomputed char'.

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.


Nov 23 '05 #1
1 2724
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_INF O_V1(computecha r);

Datum computechar(PG_ FUNCTION_ARGS) {

DateADT d1=PG_GETARG_DA TEADT(0);
DateADT d2=PG_GETARG_DA TEADT(1);

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

if(diff == 7*i)
PG_RETURN_DATEA DT(d1);
else
{
charresult=conc at(charresult,d 1);
charresult=conc at(charresult," :");
charresult=conc at(charresult,d 2);
charresult=conc at(charresult," ;");
PG_RETURN_DATEA DT(d2);
}
}
PG_FUNCTION_INF O_V1(returncomp utedchar);

Datum returncomputedc har (PG_FUNCTION_AR GS) {

PG_RETURN_TEXT_ P(charresult);
}
And then i will create the aggregate as follows (after compiling ...) :

CREATE OR REPLACE FUNCTION computechar(dat e,date) returns date as '/home/nabifadel/tempo/groupingWeeks.s o' LANGUAGE 'C' WITH (isStrict);
CREATE OR REPLACE FUNCTION returncomputedc har (date) returns text as '/home/nabifadel/tempo/groupingWeeks.s o' LANGUAGE 'C' WITH (isStrict);

CREATE AGGREGATE groupe_weeks_ag g( basetype = date, sfunc = computechar,sty pe = date, finalfunc = returncomputedc har);
The function 'computechar' will put the result in the variable 'charresult' that will be returned by the function 'returncomputed char'.

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.


Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
11929
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to post) *I'm running a total query, of the form
0
2208
by: schan | last post by:
Hi there, I was wondering if someone could shed some light on a problem I have no idea on how to fix. I created an Excel Add-In that uses an ADO connection to an Access database on a file server, which in turn has its tables linked to an Oracle back-end. I'm pretty sure I can take out Access as the middleman by just querying against the Oracle database, but that's not my question.
1
2348
by: R.A.M. | last post by:
Hello, I am learning SQL Server 2005. I have (correctly) written in .NET assembly DemoSQLServer with aggregate function AvgNoMinMax in class Demo and I have added assembly to database DemoSQLServer. Now I need to create aggregate in SQL Server. I tried this way: CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME .. Unfortunately I have error:
0
1585
by: BillCo | last post by:
just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
5
6095
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
2
6110
by: Ian825 | last post by:
I need help writing a function for a program that is based upon the various operations of a matrix and I keep getting a "non-aggregate type" error. My guess is that I need to dereference my pointers, but I'm not sure. Please help. The code: void equate(matrix *A, matrix *B) { int i, j; assert(A.row_dim == B.col_dim && A.col_dim == B.col_dim); for(i=0; i < A.row_dim; i++) for(j=0; j < A.col_dim; j++)
3
2282
by: Bartholomew Simpson | last post by:
I am writing some C++ wrappers around some legacy C ones - more specifically, I am providing ctors, dtors and assignment operators for the C structs. I have a ton of existing C code that uses these structs. A typical usage case will be as ff (note the code below is Pseudocode and WILL NOT compile) //example structs (I have left out the ctors/dtors etc for brevity sake) struct MyStructA
1
1208
by: abdlah | last post by:
I have these tables in a database: Agent(AgentPhone*, AgentName) Constituency(ConstituencyNo*,ConstituencyName, RegionCode, ConstituencyDistrict) Party(PartyCode*, PartyName) PollingStation(StationNo*,ConstituencyNo*,RegionCode, AgentPhone, StationName) Region(RegionCode, RegionName), and VoteResults(Party*, ResultsCategory*, Votes, AgentPhone) * Represents the fields that form the primary keys. Results Category are L and S...
4
1768
jhardman
by: jhardman | last post by:
I have a table that looks like this: location group agentsReady contractsCompleted location1 group1 12 14 location1 group2 12 6 location2 group1 25 12 location2 group2 25 20 location2 group3 25 2The agentsReady field is duplicating in all of the groups for the same location (in other words the 12 agentsReady in group1 are the same 12 agentsReady in group2) Grouping by location would be really easy, but what I really need is a grand total,...
0
9187
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9054
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8936
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8894
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7777
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5879
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4390
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4637
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2361
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.