Hi All!
I need to make function which emulates table and returns setof columns
of this "table"
for example, i'm making query: "SELECT * FROM my_table(user_id)"
and function
CREATE OR REPLACE FUNCTION my_table (integer)
RETURNS setof text
AS '
DECLARE
check_date date;
max_date date;
r record;
BEGIN
max_date := $2;
check_date := $3;
WHILE (check_date >= max_date) LOOP
table_nam := ''table_''||to_char(check_date, ''YYYYMMDD'');
FOR r IN EXECUTE ''SELECT COUNT(*) as cnt,
AVG(amount) as avg_amount, SUM(amount) as sum_amount
FROM ''||table_nam||''
WHERE user_id = ''||$1||'' ''
LOOP
RETURN NEXT r.cnt || r.avg_amount || r.sum_amount;
END LOOP;
check_date := check_date - interval ''1 day'';
END LOOP;
RETURN;
END;'
LANGUAGE 'plpgsql';
As a result i need to get a records, and will be able to use each
column from it
But my function doesn't work
What is wrong?
Thanx
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
