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 1qlINA-0028rV-7n for pgsql-general@arkaria.postgresql.org; Wed, 27 Sep 2023 00:25:48 +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 1qlIN8-00GWml-HF for pgsql-general@arkaria.postgresql.org; Wed, 27 Sep 2023 00:25:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qlIN8-00GWmR-5M for pgsql-general@lists.postgresql.org; Wed, 27 Sep 2023 00:25:46 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qlIN1-007mfn-2e for pgsql-general@lists.postgresql.org; Wed, 27 Sep 2023 00:25:45 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-d818d65f23cso11410116276.3 for ; Tue, 26 Sep 2023 17:25:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1695774337; x=1696379137; 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=SCmTxU9vQFMhQn1jsGdlmIu86/dBwvX/HVbiR5xCORU=; b=YIKa2w9tbZQesRHtemGqFTjDmpsZB1yM4a95XZCqeBvjsRBHoV0zN1uiR2kBEgqrkP 3ou/yxFP6/Jd97SAPv4xosyFXtUm/DPNQ/bOK9PrPXzKitDjrqlV/0rYZc77XPYAJ8Dd b7EMSD4sGdtXR/ZlLeDNYJl7oIbNRhXw9ug1gBsL+4biJKxtb4gHFYLoOreY3K+GRqWh E9qRpQXgOuNEymkGlllMaAVH5PHF+vAoapaQ3y9i+asxPR2QHr0zZ4S4Te1q4yqRbcOi gzxqstXXIW51a3ZHnfy7lCJSRIegyo1Yqxif9BA93QmFJYzYFH8NekP1veuQrRE2tbhf Re2A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1695774337; x=1696379137; 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=SCmTxU9vQFMhQn1jsGdlmIu86/dBwvX/HVbiR5xCORU=; b=XhGd2c5ZlUajwVl+oH4y66zazK32EovrNkDvIJSZAIuxa5IUX0c7i2NwA5m9I9chnL q3gdg7qTLSBEPEJRo7VXfpMG7y2SYT4vnbVHJk/Nn1Zm4GpW+Q+8vSenaqKJnCoNN8Ao WVxtKy0GV9AO0vBfyP/4mNvKxFi8KVNfnMeJ1VRgU1bEOTkhRx5zpsgN5eV3EZCh0vqH oAt9x1EZTBsxqXp+K3+sDgdpLBy5kFlxTbINZFyJM48CKjYQyjrmsZ8JKXG+3NiJ6W6G FdlRPCLnfBgjjM8mnQKjc5nk5Nk3wb375MGl4E9Omxybo7wT/deckNDnrqlXyKD7wuxr JY6g== X-Gm-Message-State: AOJu0Ywuh3U6owrHAauFt9tAX5qNoqTxtqxdXGFzl2DS44nFH7AnKJxa opZgr00M5y4svxy6tH2zCNlFmFTG8ZY= X-Google-Smtp-Source: AGHT+IFXo617TLH1DD1oNzl7lwswJZn0DNnmOoWa3OUaRVygTd5T9DjBIVtLp0vUK0p4ReDzJs92VQ== X-Received: by 2002:a25:aa8e:0:b0:d78:2383:4427 with SMTP id t14-20020a25aa8e000000b00d7823834427mr476200ybi.9.1695774337016; Tue, 26 Sep 2023 17:25:37 -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 b78-20020a25cb51000000b00babcd913630sm3065957ybg.3.2023.09.26.17.25.36 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 26 Sep 2023 17:25:36 -0700 (PDT) Message-ID: <72d8f382-90d9-bfd4-1b80-4fbe37770bd3@gmail.com> Date: Tue, 26 Sep 2023 19:25:36 -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> <538ed08d-55ba-4a51-8e46-4e424aaebb3c@aklaver.com> <623fe0c1-bdb4-49f7-9115-284159b3b137@aklaver.com> From: Ron In-Reply-To: <623fe0c1-bdb4-49f7-9115-284159b3b137@aklaver.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 9/26/23 16:29, Adrian Klaver wrote: [snip] > As a very simple example: This is EXACTLY what I was looking for.  Thank you. > > create table source(id integer, fld_1 varchar); > > insert into source values (1, 'cat'), (2, 'dog'), (3, 'fish'); > > CREATE OR REPLACE FUNCTION public.table_return(multiplier integer, suffix > character varying) >  RETURNS TABLE(multiplied integer, fld_suffix character varying, > rand_number numeric) >  LANGUAGE plpgsql > AS $function$ > DECLARE >     _id    integer; >     _fld   varchar; > BEGIN > > FOR _id, _fld IN >     SELECT >         id, fld_1 >     FROM >         source >     LOOP >         multiplied = _id * multiplier; >         fld_suffix = _fld || '_' || suffix; >         rand_number = random() * 100; > >         RETURN NEXT; >     END LOOP; > > END; > $function$ > ; > > select * from table_return(2, 'test'); >  multiplied | fld_suffix |   rand_number > ------------+------------+------------------ >           2 | cat_test   | 79.7745033326483 >           4 | dog_test   | 12.5713231966519 >           6 | fish_test  | 3.21770069680842 -- Born in Arizona, moved to Babylonia.