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@xxxxxxxxx> 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@xxxxxxxxxxx
