How to pass a rows from select to a function as args in Postgresql?-CodePudding

I have a function:

my_function(arg1 integer, arg2 text, arg3, text);

I have a view my_view that returns as a row: (arg1, arg2, arg3);

I can call my_function() with the row from the view as I would do explicitely:

select my_function(arg1 , arg2, arg3) from  my_view;

but I would like to do it somehow as mass-assignment:

select my_function(my_view.*) from  my_view;

select my_function(my_view) from  my_view

Is there any function that can do transformation for me to be able to do this call this way? Reason is I plan to have some 20-50 attributes and so writing long expression is not comfortable.

CodePudding user response：

If you don't mind calling it by select my_function('my_view'); you can overload your function and handle the argument unpacking there:

--example, base function
create or replace function my_function(arg1 int, arg2 int, arg3 int)
returns int language sql as $$
select arg1 arg2 arg3;
$$ ;

--overload to unpack a view
create or replace function my_function(arg_view_schema text, arg_view text)
returns int language plpgsql as $$ 
declare result int;
begin
   execute format('select my_function(a,b,c) from %I.%I',
                  arg_view_schema, arg_view) into result;
   return result;
end $$ ;

--overload to default to schema public when looking for the view to unpack
create or replace function my_function(arg_view text)
returns int language sql as $$ 
select my_function('public', arg_view)
$$ ;

Online demo.

CodePudding user response：

I think it will be the easiest if you create function where you pass whole record.

For example:

CREATE VIEW my_view AS
SELECT 
  1 AS foo, 
  2 AS bar,
  'hello' AS world
UNION ALL
SELECT
  10 AS foo, 
  22 AS bar,
  'hello' AS world;

CREATE FUNCTION my_function(view_row my_view)
RETURNS int
 LANGUAGE sql
AS $function$
  SELECT view_row.foo * view_row.bar
$function$
;

SELECT my_function(my_view) FROM my_view;