Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qlDl7-001nbv-06 for pgsql-general@arkaria.postgresql.org; Tue, 26 Sep 2023 19:30:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qlDl5-00EUbp-Hr for pgsql-general@arkaria.postgresql.org; Tue, 26 Sep 2023 19:30:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qlDl5-00EUbh-5y for pgsql-general@lists.postgresql.org; Tue, 26 Sep 2023 19:30:11 +0000 Received: from mail-yw1-x112b.google.com ([2607:f8b0:4864:20::112b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qlDky-006mxG-Gf for pgsql-general@lists.postgresql.org; Tue, 26 Sep 2023 19:30:09 +0000 Received: by mail-yw1-x112b.google.com with SMTP id 00721157ae682-59f1dff5298so107922997b3.3 for ; Tue, 26 Sep 2023 12:30:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1695756603; x=1696361403; darn=lists.postgresql.org; 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=QUD7i9eHiS/bx9xdR+AWQvDZaif+KXTHQk5uK7EZUx8=; b=IRedKC9CZqUfXxzErYkWzdY0pHhSW1Eyxk7qH6DUWfroEs7/w5kl4m3s38TQ+u5qZi RtSjnlCCU8NRwBtsvIHD5zGDwlK7tdoAzz2d82M6svtu9rb+/QSAGgC1F0kaA5uU6ynQ PWEN9gLMQy/x8M0Q6IttxF/LSHVplKJ7LoylobX5OThbhx1VViCVoYY/k2s/FTlgONP2 g5iJ+1Qp6zBSikHuKoFR13XK4Zh+x5kvQOMovdwhhWRR+RMlB1lI96Rt7UpIXLKSjdp8 ALoIJjDsnPqLKqQ3Oy8m6CeO5VVHxFyc0UZxqBe2poOTP1SwHJKZVQCSPsDozkkXaEeW Vs2g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1695756603; x=1696361403; 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=QUD7i9eHiS/bx9xdR+AWQvDZaif+KXTHQk5uK7EZUx8=; b=FQJbmYBj/ZuTAZj/j5nHQ/QOOnWZxvX7RkLshaXcquhDm+/UkeOqdMiMMlgA1Jhr/S GXUes69M6N7JUBYHrhX5m9d9DRhQqi/AthoKmvQkMJPBfSik0xc4xoRix7TZRzFdhJyr 6E3V+jO/5h5TBfGFg7AlZvTlBCN2hDd0MNhHzc/hL4RRrgzBjFwi50G+0a5yBm/rNeM6 O41UIh+cGDZklCTxDGBJmfaBCAGyVq0YvsZZC8VZqqkGGgz4QRCUxcL8OdBpsaGyjiRw e6bP2MuQaCeQ7OWfcw5Y3lGBVZlvR68vLVGLMB57WqDSpvMemVz0tZ08YNoX4NycKY/I OCRA== X-Gm-Message-State: AOJu0YybsXSnP9nRGa47vIxF/BH7n1Eo75jKdEjDI8yI821nPjUjUTEU QHkypM9gb7O59S1wr9YpRmYRzV9sIZE= X-Google-Smtp-Source: AGHT+IE8NnOotKP1NpDMmeh2w2pxXR8B//ARwdF25p7mAAQEscxJk+/wYPJ6NMyox4pECs8wL4ZL2Q== X-Received: by 2002:a0d:c2c2:0:b0:594:e2e6:25e8 with SMTP id e185-20020a0dc2c2000000b00594e2e625e8mr9677897ywd.48.1695756603426; Tue, 26 Sep 2023 12:30:03 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:5dc0:a698:4c4:a73d? ([2600:1700:bd2c:b810:5dc0:a698:4c4:a73d]) by smtp.googlemail.com with ESMTPSA id x125-20020a0dee83000000b0057a918d6644sm3167354ywe.128.2023.09.26.12.30.02 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 26 Sep 2023 12:30:03 -0700 (PDT) Message-ID: Date: Tue, 26 Sep 2023 14:30:02 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Ad hoc SETOF type definition? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <9707e21e-eed3-3c06-821e-e935eab4c098@gmail.com> <432138.1695750386@sss.pgh.pa.us> <73c7a357-62fe-3274-7f27-7ef9adc3b9b5@gmail.com> <436021.1695752103@sss.pgh.pa.us> From: Ron In-Reply-To: <436021.1695752103@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 9/26/23 13:15, Tom Lane wrote: > Ron writes: >> On 9/26/23 12:46, Tom Lane wrote: >>> Ron writes: >>>> Is there a way to define the SETOF record on the fly, like you do with >>>> RETURNS TABLE (f1 type1, f2 type2)? >>> Doesn't RETURNS TABLE meet the need already? >> That rationale means that RETURN SETOF is not needed, and can be removed >> from Pg, since "RETURNS TABLE meet the need already". > Indeed, we might not have invented SETOF if RETURNS TABLE were there > first ... but it wasn't. SETOF is from PostQUEL originally I think. > RETURNS TABLE is from some johnny-come-lately addition to the SQL spec. > We're not going to remove SETOF at this point. > >> So... can ad hoc SETOF definitions be created in the function definition, or >> is CREATE TYPE the only way to do it? > I'm not really sure what functionality you think is missing from RETURNS > TABLE, granting that you do want to return a set of rows and not exactly > one row. There might be some other logic in the body of the FOR loop that is not practical to embed in the body of the SELECT statement. > Admittedly, what you get is an anonymous record type and not > a named composite type, but if you want to name the type then I think > having to issue an explicit CREATE TYPE is a good thing. That makes > it clear that the type exists independently of the function. If you're going to only use that type with the function, then an anonymous record type is Good Enough. Just like anonymous DO blocks are useful. > (Our > behavior of automatically making composite types for tables seems to > me to have been a rather unfortunate choice.) It makes developers/DBAs lives just that much easier. -- Born in Arizona, moved to Babylonia.