Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 59D812E2DFE for ; Fri, 21 Dec 2007 07:52:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 16438-01-9 for ; Fri, 21 Dec 2007 07:51:54 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.180]) by postgresql.org (Postfix) with ESMTP id 0A1552E450F for ; Fri, 21 Dec 2007 07:51:59 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so479690waf.5 for ; Fri, 21 Dec 2007 03:51:58 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=+q9L1KOzyNrhCBNoQUWqRhBX/t8T5Y5nWXOwxCZ9Tx0=; b=k/a4/fsq1d/EpPjvr5PpCJ8GS2ns1NVgP6KEVeSetS2dilPCBG1BCZlmRospA4MIfACJJX/uHk/H8YFf/pm7ISusoPMp2g8TgFYlSE9YoQ7EtaJyKUWJH3k+eBbReQiAKI3npxZib+W8GndOUik+5hk4KQB3h7nNqMI1HRluy7M= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=OcZTqx//6yDYD5Xxz5PEujBxpITReT/ArrAY+F9sWEMLWVixxqElVA7bFMxa1Gk38YfhlffaNLudLNL//9nPeHYP7mvEXqW4J5l4L51BnIsLYrdDI0pqogIG3/pWySoaIEG+WbiCFtYOdJIMHt0Mh380CtXCB0AChd1muOO0XIY= Received: by 10.115.15.1 with SMTP id s1mr1338281wai.0.1198237918644; Fri, 21 Dec 2007 03:51:58 -0800 (PST) Received: by 10.114.78.12 with HTTP; Fri, 21 Dec 2007 03:51:58 -0800 (PST) Message-ID: <1f30b80c0712210351v383d9a8cxbd6d085d1646841d@mail.gmail.com> Date: Fri, 21 Dec 2007 16:51:58 +0500 From: imad To: "Leif B. Kristensen" Subject: Re: Concatenation through SQL Cc: pgsql-sql@postgresql.org In-Reply-To: <200712211216.45186.leif@solumslekt.org> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <6C0CF58A187DA5479245E0830AF84F42337F97@poweredge.attiksystem.ch> <200712211216.45186.leif@solumslekt.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/110 X-Sequence-Number: 29802 On Dec 21, 2007 4:16 PM, Leif B. Kristensen wrote: > On Friday 21. December 2007, Philippe Lang wrote: > > >(SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id > > = appointments.id) AS employees > >FROM appointments > >------------------------------ > > > >... where CONCAT suggest we want to concatenate the variable inside, > >with the separator ', ' inbetween. > > I've got a similar problem. My persons table has a number of fields for > different name parts: given, patronym, toponym, surname, occupation, > epithet. I've written a Plpgsql function that concatenates the full > name, but it's big and ugly. I'd like something more elegant, like the > Python or PHP join() function. I tried Andreas' suggestion like this: > > pgslekt=> select array_to_string(array(select given, patronym, toponym > from persons where person_id=57), ' '); > ERROR: subquery must return only one column Try concatenating your columns using the '||' operator or 'concat' function and project only one column from the subquery. You won't need to convert it to an array and back to string. --Imad Database Expert