From george.woodring@iglass.net Thu Aug 20 12:14:18 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DECF46327B9 for ; Thu, 20 Aug 2009 09:14:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 37399-03 for ; Thu, 20 Aug 2009 12:14:08 +0000 (UTC) X-Greylist: delayed 00:06:49.408144 by SQLgrey-1.7.6 Received: from mail-px0-f180.google.com (mail-px0-f180.google.com [209.85.216.180]) by mail.postgresql.org (Postfix) with ESMTP id 7CBDC6325F1 for ; Thu, 20 Aug 2009 09:14:08 -0300 (ADT) Received: by pxi10 with SMTP id 10so3418678pxi.28 for ; Thu, 20 Aug 2009 05:14:06 -0700 (PDT) MIME-Version: 1.0 Received: by 10.142.209.20 with SMTP id h20mr1491745wfg.36.1250770035839; Thu, 20 Aug 2009 05:07:15 -0700 (PDT) X-Originating-IP: [74.239.167.171] Date: Thu, 20 Aug 2009 08:07:15 -0400 Message-ID: Subject: Advice returning data as an array From: George Woodring To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd32e2eae923604719197cf X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/111 X-Sequence-Number: 33020 --000e0cd32e2eae923604719197cf Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit I have the following query that works the way I need: SELECT '20090812' AS date, sum(in_oct) AS bytes, sum(in_pkt) AS pkts, 'appl' AS name, ARRAY['appl'] AS fields, CAST((LEAST(src_port, dst_port)) AS varchar) as fieldVal FROM flow_history.flows_20090812 GROUP BY fieldVal ORDER BY bytes DESC LIMIT 20; My issue is that I need to get the fieldVal to return as an array and be fieldVal[1]. Any suggestions? Thanks, Woody -- iGLASS Networks www.iglass.net --000e0cd32e2eae923604719197cf Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I have the following query that works the way I need:

SE= LECT '20090812' AS date, sum(in_oct) AS bytes, sum(in_pkt) AS pkts,= 'appl' AS name, ARRAY['appl'] AS fields, CAST((LEAST(src_p= ort, dst_port)) AS varchar) as fieldVal FROM flow_history.flows_20090812 GR= OUP BY fieldVal ORDER BY bytes DESC LIMIT 20;

My issue is that I need to get the fieldVal =A0to retur= n as an array and be fieldVal[1]. =A0Any suggestions?

<= div>Thanks,
Woody

--
iGLASS Networks
= www.iglass.net
--000e0cd32e2eae923604719197cf-- From ejones@engineyard.com Wed Aug 26 18:40:00 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B92AB634125 for ; Wed, 26 Aug 2009 15:40:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 74975-02-5 for ; Wed, 26 Aug 2009 18:39:46 +0000 (UTC) X-Greylist: delayed 00:05:42.616777 by SQLgrey-1.7.6 Received: from smtp136.iad.emailsrvr.com (smtp136.iad.emailsrvr.com [207.97.245.136]) by mail.postgresql.org (Postfix) with ESMTP id AEAFB636B2F for ; Wed, 26 Aug 2009 15:26:24 -0300 (ADT) Received: from relay23.relay.iad.mlsrvr.com (localhost [127.0.0.1]) by relay23.relay.iad.mlsrvr.com (SMTP Server) with ESMTP id 9FA7A1B41EF; Wed, 26 Aug 2009 14:20:40 -0400 (EDT) Received: by relay23.relay.iad.mlsrvr.com (Authenticated sender: ejones-AT-engineyard.com) with ESMTPSA id 4D8E11B4026; Wed, 26 Aug 2009 14:20:40 -0400 (EDT) Cc: pgsql-sql@postgresql.org Message-Id: <3EB27E36-4935-4DDA-B365-53D72B7226AF@engineyard.com> From: Erik Jones To: George Woodring In-Reply-To: Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v936) Subject: Re: Advice returning data as an array Date: Wed, 26 Aug 2009 11:20:39 -0700 References: X-Mailer: Apple Mail (2.936) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/134 X-Sequence-Number: 33043 On Aug 20, 2009, at 5:07 AM, George Woodring wrote: > I have the following query that works the way I need: > > SELECT '20090812' AS date, sum(in_oct) AS bytes, sum(in_pkt) AS > pkts, 'appl' AS name, ARRAY['appl'] AS fields, CAST((LEAST(src_port, > dst_port)) AS varchar) as fieldVal FROM flow_history.flows_20090812 > GROUP BY fieldVal ORDER BY bytes DESC LIMIT 20; > > My issue is that I need to get the fieldVal to return as an array > and be fieldVal[1]. Any suggestions? The manual has everything you should need: http://www.postgresql.org/docs/8.4/interactive/arrays.html#ARRAYS-IO What would help us help you past that is if you show what you have already tried so we know where you need correction/help. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k