From: | "Anton Melser" <melser(dot)anton(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | temp tables and function performance |
Date: | 2006-12-15 19:06:20 |
Message-ID: | 92d3a4950612151106r7c3ffbc6o624f26d9aaf4caf8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I am trying to move up in the world with my sql and need to do the following...
I have a subscribers table and I need to export to csv (semi-colon
separated) certain fields - that is fine, but I also need to export a
multi-select field from another table as one string (0 to n values
separated by commas) per line. The problem being that to get the
actual string I have to go via 4 other relations... and as I have
200k+ subscribers this takes a while.
My idea (which seems to work, though I haven't tested fully as it
takes too damn long!), was to do the following. I would REALLY
appreciate any pointers as my sql has never been this challenged!
CREATE OR REPLACE FUNCTION mytest()
RETURNS integer AS
$BODY$DECLARE kindy INTEGER;
BEGIN
create temporary table tmp_interests(
id bigint,
value character varying(100)
)
WITHOUT OIDS
ON COMMIT DROP;
insert into tmp_interests
select distinct si.subid, rbi.value
from restem rbi, cats cc, trm_terms tt, subrest si
where rbi.key = cc.name
and cc.catid = tt.modcid
and tt.tid = si.themeid;
create temporary table tmp_subscribers(
email character varying(200),
format character varying(4),
interests character varying(1000),
)
WITHOUT OIDS
ON COMMIT DROP;
insert into tmp_subscribers
Select email,
format,
my_interests(id) as interests
from subscriber;
GET DIAGNOSTICS kindy = ROW_COUNT;
copy tmp_subscribers to '/home/myname/subs.csv' WITH CSV DELIMITER AS
';' NULL AS '';
GET DIAGNOSTICS kindy = ROW_COUNT;
return kindy;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
...
CREATE OR REPLACE FUNCTION my_interests(bigint)
RETURNS character varying AS
$BODY$DECLARE
subid ALIAS FOR $1;
interests character varying;
myinterest RECORD;
BEGIN
interests := '';
FOR myinterest IN execute 'select value from tmp_interests where id =
' || subid LOOP
if interests = '' then
interests := myinterest.value;
else
interests := interests || ',' || myinterest.value;
end if;
END LOOP;
RETURN interests;
END$BODY$
LANGUAGE 'plpgsql' VOLATILE;
...
select mytest();
If there are errors then please just focus on the logic, as I have cut
back on the number of columns (for clarity) and changed a lot of the
real table/names... Am I going about it the right way? Is there a
better way?
Thanks heaps,
Antoine
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Moran | 2006-12-15 19:08:57 | Re: FreeBSD shared memory settings |
Previous Message | Jeff Davis | 2006-12-15 19:04:44 | Re: FreeBSD shared memory settings |