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. 1 2729
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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.
|
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:
|
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
|
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
| |
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++)
|
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
|
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...
|
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,...
|
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...
|
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,...
| |
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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();...
|
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...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |