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

From Ron
Subject Re: Ad hoc SETOF type definition?
Date
Msg-id 72d8f382-90d9-bfd4-1b80-4fbe37770bd3@gmail.com
Whole thread Raw
In response to Re: Ad hoc SETOF type definition?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
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.



pgsql-general by date:

Previous
From: Jeff Janes
Date:
Subject: Re: log_statement vs log_min_duration_statement
Next
From: Erik Wienhold
Date:
Subject: Re: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION })