X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0253F9FB368 for ; Wed, 23 Aug 2006 16:44:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 22941-09 for ; Wed, 23 Aug 2006 16:44:41 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id 516D59FB346 for ; Wed, 23 Aug 2006 16:44:41 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so458136nfc for ; Wed, 23 Aug 2006 12:44:37 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=pIbnIZ4xpHgZCkcPJcCpi/2X97FtiHdJS/m2KJIkm8L8bKX3RN4rgzw70h6w37PxEfUNHvUSdgobtxA8mhOY/Z2SdQ3HufcyP01TIwvJ4MQkXSiacsmg7lijZGPHUgPW0PhaXDk0F8zDIWPbEDbdPb8RTpiXAG5jErqMfZQH9yU= Received: by 10.67.100.17 with SMTP id c17mr402266ugm; Wed, 23 Aug 2006 12:44:35 -0700 (PDT) Received: by 10.66.248.13 with HTTP; Wed, 23 Aug 2006 12:44:35 -0700 (PDT) Message-ID: <7539aebb0608231244n142bb3b4o97f82cb76a4e22ed@mail.gmail.com> Date: Wed, 23 Aug 2006 15:44:35 -0400 From: Sumeet To: "MHahn@seeandswim.com" Subject: Re: All columns from table in a joined query Cc: pgsql-sql@postgresql.org In-Reply-To: <14106936.1156360501724.JavaMail.teamon@b217.teamon.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_128289_16654420.1156362275225" References: <14106936.1156360501724.JavaMail.teamon@b217.teamon.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.449 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/170 X-Sequence-Number: 26128 ------=_Part_128289_16654420.1156362275225 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline You need to create a custom aggregate for this CREATE AGGREGATE array_accum ( sfunc = array_append, basetype = anyelement, stype = anyarray, initcond = '{}' ); then use the field names in your query like this select array_to_string(array_accum(field1 || '@' || field2),'#') as field_alias from table_name records will be seperated by '#' hope this helps. On 8/23/06, MHahn@seeandswim.com wrote: > > I've been trying to figure out how to do the following: > > Select schedule.* from schedule join meetingday on schedule.id = > meetingday.scheduleid where sessionnumber = 165 group by schedule.* order > by min(meetingday.date); > > Is this possible in any way, or do I need to list each field of the > schedule table to get all of them? > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Thanks, Sumeet Ambre Masters of Information Science Candidate, Indiana University. On 8/23/06, MHahn@seeandswim.com wrote: > > I've been trying to figure out how to do the following: > > Select schedule.* from schedule join meetingday on schedule.id = > meetingday.scheduleid where sessionnumber = 165 group by schedule.* order > by min(meetingday.date); > > Is this possible in any way, or do I need to list each field of the > schedule table to get all of them? > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Thanks, Sumeet Ambre Masters of Information Science Candidate, Indiana University. ------=_Part_128289_16654420.1156362275225 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

You need to create a custom aggregate for this

CREATE AGGREGATE array_accum (

    sfunc = array_append,

    basetype = anyelement,

    stype = anyarray,

    initcond = '{}'

);


then use the field names in your query like this


select array_to_string(array_accum(field1 || '@' || field2),'#') as field_alias from table_name


records will be seperated by '#'

hope this helps.





On 8/23/06, MHahn@seeandswim.com <MHahn@seeandswim.com> wrote:
I've been trying to figure out how to do the following:

Select schedule.* from schedule join meetingday on schedule.id = meetingday.scheduleid where sessionnumber = 165 group by schedule.* order by min(meetingday.date);

Is this possible in any way, or do I need to list each field of the schedule table to get all of them?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings



--
Thanks,
Sumeet Ambre
Masters of Information Science Candidate,
Indiana University.

On 8/23/06, MHahn@seeandswim.com <MHahn@seeandswim.com> wrote:
I've been trying to figure out how to do the following:

Select schedule.* from schedule join meetingday on schedule.id = meetingday.scheduleid where sessionnumber = 165 group by schedule.* order by min(meetingday.date);

Is this possible in any way, or do I need to list each field of the schedule table to get all of them?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings



--
Thanks,
Sumeet Ambre
Masters of Information Science Candidate,
Indiana University. ------=_Part_128289_16654420.1156362275225--