Stefano Dal Pra wrote: > suppose you have a large table tab and two (or more) queryes like this: > > SELECT count(*),A FROM tab WHERE C GROUP BY A; > SELECT count(*),B FROM tab WHERE C GROUP BY B; > > is there any way to get both results in a single query, > eventually through stored procedure? > The retrieved [count(*),A] ; [count(*),B)] data couldnt fit > on a single table, of course. > > The main goal would be to get multiple results while scanning the > table[s] once only > thus getting results in a faster way. > > This seems to me quite a common situation but i have no clue whether a neat > solution can be implemented through stored procedure. With a temp table: CREATE TEMPORARY TABLE tmp AS SELECT COUNT(*) as rows, a,b FROM WHERE C GROUP BY a,b; SELECT SUM(rows), a FROM tmp GROUP BY a; SELECT SUM(rows), b FROM tmp GROUP BY b; DROP TABLE tmp; (Using temp tables in plpgsql procedures doesn't quite work until 8.3. But you can use dynamic EXECUTE as a work-around. There used to be a FAQ entry about that, but apparently it's been removed because the problem has been fixed in the upcoming release.) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
