Hello,

I am trying to find a solution to the following problem:
Given a table:

CREATE TABLE public.test
(
id integer ,
date date,
text varchar,
value integer
)
;
INSERT INTO test VALUES (1,'2006-10-1','ENTRY1',5);
INSERT INTO test VALUES (1,'2006-11-1','ENTRY2',5);
INSERT INTO test VALUES (1,'2006-12-1','ENTRY3',5);
INSERT INTO test VALUES (2,'2006-10-1','ENTRY4',5);
INSERT INTO test VALUES (2,'2006-11-1','ENTRY5',5);


I would like to produce output like this function does:


CREATE OR REPLACE FUNCTION public.test_fn(date, date, int4)
RETURNS SETOF public.test AS
$BODY$DECLARE
sum_val int;
cur_row test%ROWTYPE;
BEGIN

sum_val := 0 ;

FOR cur_row IN
SELECT * FROM test t1
WHERE $1 <= t1.date --start
AND $2 >= t1.date --end
AND $3 = t1.id --id
LOOP
RETURN NEXT cur_row;
sum_val:= sum_val + cur_row.value ;
END LOOP;

cur_row.date := NULL;
cur_row.text := 'TOTAL :';
cur_row.value := sum_val ;
RETURN NEXT cur_row;
RETURN ;

END;$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;


SELECT * FROM test_fn('2006-9-1','2006-12-2',1);


Now I am making up my mind if it's possible to create a view to achieve this,
allowing a SELECT statement like:

SELECT *
FROM test_with_totals_v
WHERE date > '2006-9-1'
AND date < '2006-12-2'
AND id = 1

I tried a SELECT-rule using UNION to add the aggregate rows, but as the
where-clause of the calling statement gets evaluated last this will not do.


Thanks, Christoph

--

perl -e "print scalar reverse q/ed.enilno (AT) ergn (DOT) l.hc/"

Ch Lamprecht <christoph.lamprecht.no.spam (AT) web (DOT) de> wrote:
I am afraid that this will not work, and for the very reason you stated:
the WHERE clause of the query cannot influence the rows that the view
produces.

I think that your solution with the function is good, because any
other solution (query with UNION etc.) would require the 'SELECT * FROM
test' to run twice - once for the rows, and once for the summary.

Yours,
Laurenz Albe