Skip to content

Instantly share code, notes, and snippets.

@drbobbeaty
Created February 4, 2013 16:52
Show Gist options
  • Save drbobbeaty/4707934 to your computer and use it in GitHub Desktop.
Save drbobbeaty/4707934 to your computer and use it in GitHub Desktop.
I needed a way to load up the children of a very large table with decent referential integrity a lot faster than joins and sub-selects. What I came up with was a stored procedure that loops over the parent ids, and loads up the results into a temp table.
create or replace function locations_for_demand_set(ds_id uuid)
returns table(id uuid,
demand_id uuid,
is_anchor_zip boolean,
latitude double precision,
longitude double precision,
name varchar,
subdivision_rank varchar,
type varchar,
division_permalink varchar,
zip varchar) as $body$
declare
rec demands%rowtype;
begin
create temp table answer on commit drop as
select * from locations
with no data;
for rec in select * from demands where demand_set_id = ds_id
loop
insert into answer
select *
from locations l
where l.demand_id = rec.id;
end loop;
return query select * from answer;
end
$body$ language plpgsql
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment