From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Ad hoc SETOF type definition? |
Date: | 2023-09-27 00:25:36 |
Message-ID: | 72d8f382-90d9-bfd4-1b80-4fbe37770bd3@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Wienhold | 2023-09-27 02:03:32 | Re: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION }) |
Previous Message | Jeff Janes | 2023-09-26 23:51:31 | Re: log_statement vs log_min_duration_statement |