Re: [HACKERS] Is this possible? concatenating results from a subquery

[Janko Richter]

Perhaps this helps:
CREATE AGGREGATE concat (
    BASETYPE = text,
    SFUNC    = textcat, -- is function of operator 'text || text'
    STYPE    = text,
    INITCOND = ''
);
SELECT
 P.personid,
 P.name,
 concat( N.note ) AS allnotesbythisperson
FROM tblperson AS P
INNER JOIN tblnotes AS N ON N.personid=P.personid
WHERE P.personid=34
GROUP BY P.personid, P.name;
Regards, Janko
Erwin Moller wrote:
Hi!
I face the following problem:
2 tables: tblperson and tblnotes
tblperson:
colums: personid (PK), name
tblnotes:
colums: noteid(PK), personid(references tblperson(personid)), note
tblnotes has notes stored written by a person from tblperson identified (FK) 
by its personid.

I make a select on one table with certain criteria and want to have a 
concatenation on a subquery results.
Something like this:

SELECT 
 P.personid, 
 P.name,
 concat(SELECT N.note FROM tblnotes AS N WHERE (N.personid=P.personid) ) 
     AS allnotesbythisperson
  FROM tblperson AS P WHERE (P.personid=34);

The concat word I use is pure fantasy.
Is this at all possible?
I know I can easily circumvent te problem by my scriptinglanguage (PHP), but 
that will result in many extra queries.

How do I proceed?
TIA!!
Regards,
Erwin Moller
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match