Skip site navigation (1) Skip section navigation (2)

Unflatten array

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: pg novice <pgsql-novice(at)postgresql(dot)org>
Subject: Unflatten array
Date: 2007-11-02 18:49:14
Message-ID: 472B712A.8050007@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
I am using xpath queries against an XML column like so:

annodb=# select id,xpath('//LastName/text()',content) from medlinexml
limit 10;
    id    |           xpath
----------+----------------------------
 12236137 | {Mikulicz-radecki,Freund}
 12305346 | {Ota}
 12332250 | {Bernstein}
 12255346 | {Novak}
 12255369 | {Kurzrok}
 12332284 | {Davis}
 12332297 | {Allen,Pratt,Newell,Bland}
 12305416 | {Goldstein}
 12305417 | {Seibels}
 12305424 | {Richardson}
(10 rows)

I would like to build a view where each id is associated with a text
representation of each last name.  So, the view would look like:

12236137  | Mikulicz-radecki
12236137  | Freund
12305436  | Ota

And so on.  Is there a way to do this without a pl/pgsql loop?

Thanks,
Sean

pgsql-novice by date

Next:From: G. J. WalshDate: 2007-11-03 00:13:18
Subject: Uncertain about recoding prepared statements from MySQL toPostgreSQL
Previous:From: Alan HodgsonDate: 2007-11-02 18:39:28
Subject: Re: need some help with an opennms query/insert

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group