* Adarsh Sharma wrote:
Christian Ullrich wrote:
Write a set of functions to get the higher-level structures (country
for states, etc.) for any given record, and put a trigger on the table
that populates the fields on insert and update.
All the world data is populated in the places table. Now I don't think
insert occurs anymore now.
Then add the new fields to the table and update them from the function
results.
Sort of a materialized view. If you have little query activity on the
table, create a view that calls the functions.
yes a function is must needed for this problem, bt any idea about the
flow of the function.
Simple version:
create function get_ancestor(p_woe_id varchar, p_level varchar) returns
text language plpgsql as $$
declare
v_woe_id varchar;
v_name text;
v_place_type varchar;
v_parent varchar;
begin
select woe_id, place_type, parent_woe_id into v_woe_id, v_place_type,
v_parent from places where woe_id = p_woe_id;
if (not found or v_place_type = p_level) then
return null;
else
while (found and v_place_type != p_level) loop
select "name", place_type, parent_woe_id into v_name,
v_place_type, v_parent from places where woe_id = v_parent;
end loop;
if found then
return v_name;
else
return null;
end if;
end if;
end;$$;
select get_ancestor('15', 'State') as state, get_ancestor('15',
'District') as district;
You could produce a more refined version using WITH RECURSIVE, but if
your table is already complete and all you need is to put in the
denormalized data, this will work just as well. Beware of cyclical
references.
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
