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

Question

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;

or

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.

You can overload the function to accept a name of your view, then extract the arguments from the view at the beginning of the function body. — Zegarek
– Zegarek, Commented Nov 16, 2022 at 10:57

Zegarek · Accepted Answer · 2022-11-16 11:22:35Z

1

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.

edited Nov 16, 2022 at 11:22

answered Nov 16, 2022 at 11:13

Zegarek

30.1k5 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

puchal · Accepted Answer · 2022-11-16 11:00:01Z

0

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;

answered Nov 16, 2022 at 11:00

puchal

2,39117 silver badges29 bronze badges

4 Comments

Petr Ekl Over a year ago

Thanks, but I know this style, I have allready found it somewhere here. My first problem is, that I need to rewrite functions which I can do, but next, bigger problem is when I have more different views that return the same structure... and I want to feed them the same function. I plan to have more views to feed the function from different sources.

Zegarek Over a year ago

@PetrEkl If you cannot use this specific approach, it's good to point that out in your question, along with your reasoning. If you don't state what you know, tried and dismissed, we'll have to keep guessing. You can just edit the above into your question.

Petr Ekl Over a year ago

Confirmed, even when the views re the same, just different name, function won't be found and used: ERROR: function my_function(my_view1) does not exist LINE 1: SELECT my_function(my_view1) FROM my_view1; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. I wonder there's nothing like UNNEST() or so... or if it is possible to share some type between views....

puchal Over a year ago

@PetrEkl there weren't specified that you need to use different views. Are those views returning different amount of columns? Are names of those columns different accross views? If all column names are the same in views or at least they are in specific order maybe it would make sense to pass record_to_json(my_view)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related