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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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