Re: [SQL] to_date function
"A. Kretschmer" <[EMAIL PROTECTED]> writes: > am Fri, dem 07.09.2007, um 17:22:30 +1200 mailte anru chen folgendes: >> seems like "to_date" function only work correctly for current month. > No, seems like to_char only work corrently if the length of the month > correctly... You need to use FMMonth not Month if you want it to be lax about the width of the month field. (I think we decided that's a bug and 8.3 will take it either way, but existing releases want the FM.) regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Failing join with set returning function
Please, remove my address from this Mailing List. thanks -- Leggi GRATIS le tue mail con il telefonino i-modeĀ di Wind http://i-mode.wind.it/
[SQL] SERIAL type's sequence is double-called or ?
Hello all,
I have a question with the SERIAL type. I want to use it for default
identification of table entries:
create table chuwee (
num serial primary key,
mesg varchar(50) not null
);
And all the inserts to this table I'm gonna log to this table:
create table chuwee_log (
id serial primary key,
date timestamp default current_timestamp,
num integer not null,
mesg varchar(50) not null
);
Logging will be done with this rule:
create or replace rule chuwee_rule as on insert to chuwee
do insert into chuwee_log (num, mesg)
values (new.num, new.mesg);
And all seems to work, but a sequence is called twice for inserts, and
logged "num"-s get a +1 value:
sunline=# insert into chuwee (mesg) values ('Hello, world!');
INSERT 9596671 1
sunline=# insert into chuwee (mesg) values ('Aquarium full of tequilla');
INSERT 9596673 1
sunline=# select * from chuwee_log;
id |date| num | mesg
++-+---
1 | 2007-09-08 16:18:26.707384 | 2 | Hello, world!
2 | 2007-09-08 16:18:58.711599 | 4 | Aquarium full of tequilla
(2 rows)
sunline=# select * from chuwee;
num | mesg
-+---
1 | Hello, world!
3 | Aquarium full of tequilla
(2 rows)
How it can be cured, if any?
Best dishes,
Aleksandr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] SERIAL type's sequence is double-called or ?
Aleksandr Vinokurov wrote: Logging will be done with this rule: create or replace rule chuwee_rule as on insert to chuwee do insert into chuwee_log (num, mesg) values (new.num, new.mesg); Don't do logging with rules, do logging with triggers. See mailing-list archives for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Failing join with set returning function
I've written a function that calculates the number of days for every month in a
given range and returns that as a set of records.
CREATE OR REPLACE FUNCTION general_daysinmonth(
date1 IN date,
date2 IN date,
month OUT date,
days OUT integer) RETURNS SETOF record AS
$body$
DECLARE
startdate date;
enddate date;
BEGIN
IF date1 >= date2 THEN
startdate := date2;
enddate := date1;
ELSE
startdate := date1;
enddate := date2;
END IF;
month := date_trunc('month', startdate);
WHILE month <= enddate LOOP
days := LEAST(general_lastdayofmonth(month), enddate) - GREATEST(startdate,
month) + 1;
RETURN NEXT;
month := month + interval '1 month';
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
Now I want to use that resultset as part of a join with a table called billing
(see definition below).
This join should return a record per month that is between salesstartdate and
salesenddate and lineamountmst should be divided pro rata the number of days in
each month
An example
If table billing holds a record like
id invoiceid inventtransid invoicedate dataareaid lineamountmst dimension
itemid salesunit issues salesstartdate salesenddate salesstopcode salespoolid
1170 22428431 735706 2006-02-28 hlm 89,89 nlaatoal-6 nlaatoal 3m 0 2006-02-24
2006-05-23 SWI-TRM-1Y aans
Then select * from general_daysinmonth(date '2006-02-24', date '2006-05-23')
would return
month days
2006-02-01 5
2006-03-01 31
2006-04-01 30
2006-05-01 23
So my join should return 4 records like
invoiceid billingmonth revenuemonth revenue
22428431 2006-02-01 2006-02-01 5,05
22428431 2006-02-01 2006-03-01 31,31
22428431 2006-02-01 2006-04-01 30,30
22428431 2006-02-01 2006-05-01 23,23
where 89,89 / number of days between 2006-02-24 and 2006-05-23 * number of days
in 2006-02 = 5,05 for the first record
I thought I could do that quite easily like this
SELECT B.invoiceid, date_trunc('month', B.invoicedate) as billingmonth,
C.month as revenuemonth,
B.lineamountmst * C.days / (B.salesenddate - B.salesstartdate +
1) as revenue
FROM billing B, general_daysinmonth(B.salesstartdate, B.salesenddate) C
WHERE B.dataareaid = 'hlm' AND
B.issues = 0 AND
B.salesenddate >= date_trunc('year', current_timestamp) - interval '1
year';
or like this
SELECT B.invoiceid, date_trunc('month', B.invoicedate) as billingmonth,
C.month as revenuemonth,
B.lineamountmst * C.days / (B.salesenddate - B.salesstartdate +
1) as revenue
FROM billing B, (select * from general_daysinmonth(B.salesstartdate,
B.salesenddate)) C
WHERE B.dataareaid = 'hlm' AND
B.issues = 0 AND
B.salesenddate >= date_trunc('year', current_timestamp) - interval '1
year';
But I get an error message in both cases: respectively
ERROR: function expression in FROM may not refer to other relations of same
query level
ERROR: subquery in FROM may not refer to other relations of same query level
Can anyone please explain me why I'm getting this error message and how I do
what I'm trying to do?
Thanks for your help, advice and time...
CREATE TABLE public.billing (
id serial PRIMARY KEY,
invoiceid varchar(20) DEFAULT NULL,
inventtransid varchar(20) DEFAULT NULL,
invoicedate date NOT NULL,
dataareaid varchar(3) NOT NULL,
lineamountmst numeric(32,16) NOT NULL,
dimension varchar(16) NOT NULL,
itemid varchar(20) NOT NULL,
salesunit varchar(10) NOT NULL,
issues numeric(2) DEFAULT 0,
salesstartdate date NOT NULL,
salesenddate date NOT NULL,
salesstopcode varchar(16) DEFAULT NULL,
salespoolid varchar(10) NOT NULL
) WITH (fillfactor=75);
Re: [SQL] to_date function
am Fri, dem 07.09.2007, um 10:25:57 -0400 mailte Tom Lane folgendes: > "A. Kretschmer" <[EMAIL PROTECTED]> writes: > > am Fri, dem 07.09.2007, um 17:22:30 +1200 mailte anru chen folgendes: > >> seems like "to_date" function only work correctly for current month. > > > No, seems like to_char only work corrently if the length of the month > > correctly... > > You need to use FMMonth not Month if you want it to be lax about the > width of the month field. (I think we decided that's a bug and 8.3 Oh, cool, it works. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
