How can I write a function with two tables inputs and one table output in PostgreSQL?

user_13872512 0
Viewed 67

I want to create a function that can create a table, in which part of the columns is derived from the other two tables.

input table1: This is a static table for each loan. Each loan has only one row with information related to that loan. For example, original unpaid balance, original interest rate...

| id  | loan_age | ori_upb | ori_rate | ltv |
| --- | -------- | ------- | -------- | --- |
| 1   | 360      | 1500    | 4.5      | 0.6 |
| 2   | 360      | 2000    | 3.8      | 0.5 |

input table2: This is a dynamic table for each loan. Each loan has seraval rows show the loan performance in each month. For example, current unpaid balance, current interest rate, delinquancy status...

| id | month| cur_upb | cur_rate |status|
| ---| ---  | ------- | -------- | ---  |
| 1  | 01   | 1400    | 4.5      | 0    |
| 1  | 02   | 1300    | 4.5      | 0    |
| 1  | 03   | 1200    | 4.5      | 1    |
| 2  | 01   | 2000    | 3.8      | 0    |
| 2  | 02   | 1900    | 3.8      | 0    |
| 2  | 03   | 1900    | 3.8      | 1    |
| 2  | 04   | 1900    | 3.8      | 2    |

output table: The output table contains information from table1 and table2. Payoffupb is the last record of cur_upb in table2. This table is built for model development.

| id | loan_age | ori_upb | ori_rate | ltv | payoffmonth| payoffupb | payoffrate |lastStatus | modification |
| ---| -------- | ------- | -------- | --- | ---------- | --------- | ---------- |---------- | ------------ |
| 1  | 360      | 1500    | 4.5      | 0.6 |   03       | 1200      |  4.5       |  1        |  null        |
| 2  | 360      | 2000    | 3.8      | 0.5 |   04       | 1900      |  3.8       |  2        |  null        |

Most columns in the output table can directly get or transferred from columns in the two input tables, but some columns can not get then leave blank.

My main question is how to write a function to take two tables as inputs and output another table? I already wrote the feature transformation part for data files in 2018, but I need to do the same thing again for data files in some other years. That's why I want to create a function to make things easier.

postgresql function
1 Answers

As you want to insert the latest entry of table2 against each entry of table1 try this

    insert into table3 (id,  loan_age,  ori_upb,  ori_rate,  ltv,  
                        payoffmonth, payoffupb,  payoffrate, lastStatus )
    select distinct on (t1.id) 
           t1.id, t1.loan_age, t1.ori_upb, t1.ori_rate, t1.ltv, t2.month, t2.cur_upb,
           t2.cur_rate, t2.status 
    from 
        table1 t1 
    inner join 
        table2 t2 on t1.id=t2.id 
    order by t1.id , t2.month desc

DEMO1

EDIT for your updated question:

Function to do the above considering table1, table2, table3 structure will be always identical.

create or replace function insert_values(table1 varchar, table2 varchar, table3 varchar)
returns int as $$
declare 
count_ int;
begin

execute format('insert into %I (id,  loan_age,  ori_upb,  ori_rate,  ltv,  payoffmonth, payoffupb,  payoffrate, lastStatus )
select distinct on (t1.id) t1.id, t1.loan_age, t1.ori_upb,
t1.ori_rate,t1.ltv,t2.month,t2.cur_upb, t2.cur_rate, t2.status 
from %I t1 inner join %I t2 on t1.id=t2.id order by t1.id , t2.month desc',table3,table1,table2);
GET DIAGNOSTICS count_ = ROW_COUNT;
return count_;
end;
$$
language plpgsql

and call above function like below which will return the number of inserted rows:

select * from insert_values('table1','table2','table3');

DEMO2

user_77724830
Related