From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Ad hoc SETOF type definition? |
Date: | 2023-09-26 21:29:46 |
Message-ID: | 623fe0c1-bdb4-49f7-9115-284159b3b137@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Wienhold | 2023-09-26 21:47:49 | Re: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION }) |
Previous Message | Adrian Klaver | 2023-09-26 20:29:23 | Re: Ad hoc SETOF type definition? |