Re: Ad hoc SETOF type definition?

On 9/26/23 13:29, Adrian Klaver wrote:
> On 9/26/23 12:30, Ron wrote:
>> On 9/26/23 13:15, Tom Lane wrote:
>>> Ron <ronljohnsonjr(at)gmail(dot)com> writes:
>>>> On 9/26/23 12:46, Tom Lane wrote:
>
>>> 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.
>
> I think you are conflating RETURNS TABLE and RETURN QUERY. You can build
> a 'TABLE' from variables outside of a query.
>

As a very simple example:

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
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com