Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXqkT-0007zJ-Dm for pgsql-general@arkaria.postgresql.org; Thu, 02 Mar 2023 21:46:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pXqkQ-000550-LO for pgsql-general@arkaria.postgresql.org; Thu, 02 Mar 2023 21:45:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXqkQ-00054l-9F for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 21:45:58 +0000 Received: from mail-pj1-x1032.google.com ([2607:f8b0:4864:20::1032]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pXqkM-0002rJ-G2 for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 21:45:57 +0000 Received: by mail-pj1-x1032.google.com with SMTP id kb15so551511pjb.1 for ; Thu, 02 Mar 2023 13:45:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677793551; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=tA4pngLQkymw2oRmUZbb2iIK+xDCB3CU0+AqMOty798=; b=ZyzGgzQzI9mbCM+wrApGo8zMybUiIz4HVaCm5+qfI+yKsRYgdaEXJMH6+0XWcw1LL4 kCjFpN/A+mddnXFC2TraBkdNEVCazU5jOlo78X9aq4OrB0HT40resnpQonZieHq76A6a vXBQyiMVZBKasPo50OCnQNr7b5ccDTgIOoLpsfuYmNtHij3L0wC3HfjoYmFArfd9pQdq h389LDbAEtUcXZpBRXN1vThlpEHNV5NmS89Q8dLPxj7oopuUnZAfmcqgHhS1pSxCZ64j IEWFkhLUMRFa74upCp/Z23hJYGp9jwZwBcsYS6SZ9uBoST0KmXUc1PXYKWcepbnBdyMu 8Mcw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677793551; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=tA4pngLQkymw2oRmUZbb2iIK+xDCB3CU0+AqMOty798=; b=WICeFlL8MkbAoqPwkPiYJPcNl7CCZI8ow9N0FdH2kzCn/YWy7jDUQy/8ru1y4Wzitb YZ+XNhOrml4h+rEEnNI7GFS+njYVO10Ql68hGYehygtfVxIkAIP5cdPjcXyqK7r5VjGp 3v8+jOt5DgXa5gM4YvA+aZWPBfxhdAI1MHYgGAjf6rehLJ5feyXsnt2hpcbcjI2BVCmx TKfq0b9NkXex673FyeM6irkzn8lC5dEJAQecxlJkFk2P186GAkIvOUdSZAfCNolrlDfb 4Iju6THbMFN9UZ5DbzSnp68V670/yZf9kE4UrZDsA2SSH/2R32K9JdSQxpfM112rp/g0 uX2A== X-Gm-Message-State: AO0yUKU85ojrmFIgB3S+Pgfivqv8XqxGmkRFOoXBlG5WfmQyLvYN+5Iq 81d2tuMaZXeAqHaWX7tQVpc2PkgnAGsu0Q== X-Google-Smtp-Source: AK7set8RFUmXjbn5FmDhDIxZ/q1CrvA2WzE6ju501r6/xagabpdAkuR4zv5Qgioo1b8GvtLLTzxhog== X-Received: by 2002:a05:6a20:3949:b0:bc:bdb1:165a with SMTP id r9-20020a056a20394900b000bcbdb1165amr14151718pzg.39.1677793551309; Thu, 02 Mar 2023 13:45:51 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:6166:26b2:10a4:dd2e? ([2601:681:5a00:f790:6166:26b2:10a4:dd2e]) by smtp.gmail.com with ESMTPSA id p3-20020a62ab03000000b005a9cb8edee3sm158438pff.85.2023.03.02.13.45.50 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 02 Mar 2023 13:45:50 -0800 (PST) Message-ID: Date: Thu, 2 Mar 2023 14:45:49 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.7.1 Subject: Re: Converting row elements into a arrays? Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: <788ab733-d0d1-7791-fb22-7c2724b08816@gmail.com> From: Rob Sargent In-Reply-To: <788ab733-d0d1-7791-fb22-7c2724b08816@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/2/23 13:58, Ron wrote: > Postgresql 12.13 > > Given the sample below, I'm looking for how to generate this output.  > It's like GROUP BY, but generating an array instead of an aggreate > number. >  f1 | f2_array > ----+--------- > 1 | {1,2,3} >   2 | {1,2,3,4} >   3 | {1,2} > > The ultimate goal is to somehow use pg_index.indkey to get column > names from pg_attribute. > > create table foo (f1 int, f2 int); > insert into foo values (1, 1); > insert into foo values (1, 2); > insert into foo values (1, 3); > insert into foo values (2, 1); > insert into foo values (2, 2); > insert into foo values (2, 3); > insert into foo values (2, 4); > insert into foo values (3, 1); > insert into foo values (3, 2); > > select * from foo order by f1, f2; >  f1 | f2 > ----+---- >   1 |  1 >   1 |  2 >   1 |  3 >   2 |  1 >   2 |  2 >   2 |  3 >   2 |  4 >   3 |  1 >   3 |  2 > (9 rows) > In which environment are you accessing that array?  psql only? > >