From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | "Isaac Dover" <isaacdover(at)gmail(dot)com> |
Subject: | Re: nesting XmlAgg |
Date: | 2008-07-03 13:15:39 |
Message-ID: | 200807031515.40559.peter_e@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Am Donnerstag, 3. Juli 2008 schrieb Isaac Dover:
> select
> XmlElement(name "Catalog",
> XmlElement(name "Tables",
> XmlAgg(XmlElement(name "Table", XmlAttributes(T.table_name as
> "Name"), XmlElement(name "Columns",
> -- i was attempting to aggregate here as well
> (select XmlElement(name "Column", C.Column_Name))
> )
> ))
> )
> )
> from information_schema.tables T
> inner join information_schema.columns C
> on T.table_name = C.table_name and T.table_schema = C.table_schema
> where T.table_schema = 'public'
Try this:
select
XmlElement(name "Catalog",
XmlElement(name "Tables",
XmlAgg(XmlElement(name "Table", XmlAttributes(T.table_name as "Name"),
XmlElement(name "Columns",
(select XmlAgg(XmlElement(name "Column", C.Column_Name)) from
information_schema.columns C where T.table_name = C.table_name and
T.table_schema =
C.table_schema) )
))
)
)
from information_schema.tables T
where T.table_schema = 'public';
From | Date | Subject | |
---|---|---|---|
Next Message | David Garamond | 2008-07-04 12:35:57 | query: last N price for each product? |
Previous Message | Isaac Dover | 2008-07-03 02:08:06 | nesting XmlAgg |