From: Mina R Waheeb <syncer@gmail.com>
To: pgsql-sql@postgresql.org
Subject: SQL/XML Multi table join question
Date: Wed, 16 Apr 2008 20:34:43 +0200
Message-ID: <275890790804161134k2921148dy60206d890a047d46@mail.gmail.com> (raw)
Hi all,
I have the following tables (parent and two children)
CREATE SEQUENCE person_seq;
CREATE TABLE person(
_id integer DEFAULT nextval('person_seq') NOT NULL,
_timestamp TIMESTAMP NOT NULL,
_lastModified TIMESTAMP NOT NULL,
name VARCHAR(255) NOT NULL,
age INTEGER DEFAULT NULL,
PRIMARY KEY (_id)
);
CREATE TABLE person_nationality(
_id serial NOT NULL,
_parent INTEGER NOT NULL,
nationality VARCHAR(255) NOT NULL,
FOREIGN KEY (_parent) REFERENCES person (_id) ON DELETE CASCADE,
PRIMARY KEY (_id)
);
CREATE TABLE person_variables(
_id serial NOT NULL,
_parent INTEGER NOT NULL,
variable VARCHAR(255) DEFAULT NULL,
value VARCHAR(255) DEFAULT NULL,
FOREIGN KEY (_parent) REFERENCES person (_id) ON DELETE CASCADE,
PRIMARY KEY (_id)
);
I'm trying to generate XML element for each person which also contains
the person nationality and variables in one result set
SELECT
XMLROOT (
XMLELEMENT (
NAME information,
XMLATTRIBUTES (
person._id AS pid
),
XMLAGG(
XMLELEMENT(
name "nationality",
person_nationality.nationality
)
),
XMLAGG(
XMLELEMENT(
name "value",
person_variables.value
)
)
),
VERSION '1.0',
STANDALONE YES
)
FROM person
INNER JOIN person_nationality ON person_nationality._parent = person._id
INNER JOIN person_variables ON person_variables._parent = person._id
GROUP BY person._id
LIMIT 100 OFFSET 10000;
The above query return number of variables * nationality for each
person which is expected (for me) because of the join logic. Also i
can't use DISTINCT keyword in XMLAGG function.
Any idea how to do this? or is there is any aggregate function returns
array from row set, i didn't find any in the documenation.
Thanks in advance
Mina.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: syncer@gmail.com
Subject: Re: SQL/XML Multi table join question
In-Reply-To: <275890790804161134k2921148dy60206d890a047d46@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox