skip to Main Content

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.

2

Answers


  1. 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;
    
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search