Re: Ad hoc SETOF type definition? - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Ad hoc SETOF type definition?
Date
Msg-id 623fe0c1-bdb4-49f7-9115-284159b3b137@aklaver.com
Whole thread Raw
In response to Re: Ad hoc SETOF type definition?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Ad hoc SETOF type definition?
List pgsql-general
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@gmail.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.klaver@aklaver.com




pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Ad hoc SETOF type definition?
Next
From: Erik Wienhold
Date:
Subject: Re: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION })