How to create an SQL function that returns the table with an extra column, filled with values, based on a condition?

Question

I have a "code_table" containing uinque integers (code) and unique texts (name) in pairs. I have several tables where I have the codes, but I don't have the names. I would like to create a function that returns the table I'm intented to change, but it also adds a column that has the names matching to the codes. I can manage this doing one by one, but I would need a function with an argument for the name of the table.

So for example here's a code_table :

code     name
(int)    (text)
1    ;  "Blue"
2    ;  "Yellow"
3    ;  "Red"
4    ;  "Green"
5    ;  "Purple"
6    ;  "Black"
7    ;  "White"
8    ;  "Gray"

I also have an example table - table1:

id  something  code
(int) (text) (int)
1 ; "someinfo" ; 4
2 ; "someinfo" ; 2
3 ; "someinfo" ; 6
4 ; "someinfo" ; 1
5 ; "someinfo" ; 8
6 ; "someinfo" ; 4
7 ; "someinfo" ; 2
8 ; "someinfo" ; 2
9 ; "someinfo" ; 3

I've created a function than doesn't have a return value, but it does what I wanted:

CREATE FUNCTION add_code_name() 
RETURNS VOID AS
$BODY$
BEGIN
    ALTER TABLE table1 ADD name text;
    EXECUTE 'UPDATE table1 SET name = code_table.name FROM code_table WHERE table1.code = code_table.code';
END;
$BODY$
LANGUAGE plpgsql;

As a result I got:

id  something  code name
(int) (text) (int) (text)
1 ; "someinfo" ; 4 ; "Green"
2 ; "someinfo" ; 2 ; "Yellow"
3 ; "someinfo" ; 6 ; "Black"
4 ; "someinfo" ; 1 ; "Blue"
5 ; "someinfo" ; 8 ; "Gray"
6 ; "someinfo" ; 4 ; "Green"
7 ; "someinfo" ; 2 ; "Yellow"
8 ; "someinfo" ; 2 ; "Yellow"
9 ; "someinfo" ; 3 ; "Red"

MY QUESTION: Is there a way to put a table's name in the functions argument somehow, so it would do the same to any of my tables? Also I would put there the name of the column containing the code Something like this:

add_code_name(table1.code) 

Thank you for your help!

Answer 1

Here is a function to do the alter and update:

create or replace function add_code_name(rel text, code_col text)
returns void as 
$Body$
begin
    execute 'ALTER TABLE '||quote_ident(rel)||' ADD name text';
    execute 'UPDATE '||quote_ident(rel)||' SET name = code_table.name FROM code_table WHERE '||quote_ident(rel)||'.'||quote_ident(code_col)||' = code_table.code';

    return;
end;
$Body$
language plpgsql; 

you run it :

select add_code_name('table1', 'code');