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 1jMIO0-0006o8-Fq for pgsql-bugs@arkaria.postgresql.org; Wed, 08 Apr 2020 21:37:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jMINz-0003zc-Fe for pgsql-bugs@arkaria.postgresql.org; Wed, 08 Apr 2020 21:37:27 +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 1jMIKy-0000xM-Pf for pgsql-bugs@lists.postgresql.org; Wed, 08 Apr 2020 21:34:20 +0000 Received: from ook.raf.org ([139.99.156.21]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jMIKv-0005Eh-Dj for pgsql-bugs@lists.postgresql.org; Wed, 08 Apr 2020 21:34:19 +0000 Received: from localhost (localhost [127.0.0.1]) by ook.raf.org (Postfix) with ESMTP id E122F5DE30 for ; Thu, 9 Apr 2020 07:34:13 +1000 (AEST) X-Virus-Scanned: Debian amavisd-new at ook.raf.org Received: from ook.raf.org ([127.0.0.1]) by localhost (ook.raf.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Eq52wld-nBMJ for ; Thu, 9 Apr 2020 07:34:13 +1000 (AEST) Received: by ook.raf.org (Postfix, from userid 1001) id B3DDC5DEB1; Thu, 9 Apr 2020 07:34:13 +1000 (AEST) Date: Thu, 9 Apr 2020 07:34:13 +1000 From: raf To: pgsql-bugs@lists.postgresql.org Subject: Re: ERROR: cannot pass more than 100 arguments to a function Message-ID: <20200408213413.kyohygx2ick2zo7f@raf.org> References: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Ján Máté wrote: > Hi PostgreSQL, > > I noticed an interesting problem with the default max. number of > function arguments, especially related to JSON processing. > > I understand that there is a need to limit the max. number of > arguments for functions, but the current limit (100) is simply too > restrictive for tables with large number of columns (according to my > findings it is >250 depending on column types). > > A simple example: > > - I have a table with 70 columns > > - I want to select the most simple JSON output from that table: > > SELECT > json_build_object( > 'a', "col1", > 'b', "col2", > 'c', "col2", > ... > ) > FROM "schema"."table"; > > - with the 100 arguments limit I can select only 50 columns - 50 x > (jsonKeyName + ColumnName) = 100 arguments > > - if the limit of columns in the table is >250, why I cannot select at > least 250 columns using the built-in json_build_object(...) function? > > > Please someone consider to increase the default limit from 100 to at > least 500 (2x max. number of columns) for json_build_object(...) and > other similar JSON functions. > > Kind regards, > JM If you need that many arguments to represent the columns in a table, then you can use a single argument whose type is the table. e.g. create table t (...); create or replace function f(a t) ...; select * from f(row(...)); If you need that many arguments, and they don't represent a table, you can create an empty table just for its type definition and then do the above. cheers, raf