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!
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');