* 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
