* Adarsh Sharma wrote:
Today I am facing a simple problem that I fail to solve after 2 day try.
I have a places table in database whose structure is as :
CREATE TABLE places
(
woe_id character varying(15) NOT NULL,
iso character varying(6),
"name" text,
"language" character varying(6),
place_type character varying,
parent_woe_id character varying(15),
lat numeric(12,8),
lon numeric(12,8)
CONSTRAINT places_pkey PRIMARY KEY (woe_id)
)WITH ( OIDS=FALSE);
It's simple *name *column contains the name of places in a hierarchical
order.
fore.g
*woe_id iso name language places_type parent_woe_id lat lon
1 ZZ Earth ENG Supername 0 13.3445 234.666
10 IN INDIA ENG Country 1 12.44 234.667
11 IN J&K ENG State 10 4535.56 3453.77
12 IN Udhanput ENG District 11 1222 3443.8
15 IN Parth ENG Town 12 111.6 1222.5
*I hope U understand what i am trying to explain.
Now I want this data in the same table in extra columns fore.g
*woe_id iso name language places_type parent_woe_id lat lon town
district state country
1 ZZ Earth ENG Supername 0 13.3445 234.666
10 IN INDIA ENG Country 1 12.44 234.667
11 IN J&K ENG State 10 4535.56 3453.77 **INDIA*
*12 IN Udhanput ENG District 11 1222 3443.8 **J&K **INDIA*
*15 IN Parth ENG Town 12 111.6 1222.5 **Udhanput **J&K **INDIA*
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. Sort of a materialized view.
If you have little query activity on the table, create a view that calls
the functions.
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
