* 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
