I have two tables like this:
CREATE TABLE table1_lang ( id serial NOT NULL, lang1 character varying, lang2 character varying, ... ) CREATE TABLE table2_lang ( id serial NOT NULL, table1_id NOT NULL, lang1 character varying, lang2 character varying, ... ) Each of the lang columns contains a name in different languages. I want to create a function that returns the selected lang from table1_lang if it exist. If the name does not exist in table1_lang then the function should return the name from table2_lang.
So far I have solved my problem with this function in plpgsql:
CREATE OR REPLACE FUNCTION get_name(integer, text) RETURNS character varying AS $BODY$ DECLARE in_id ALIAS FOR $1; lang ALIAS FOR $2; table1_name varchar; table2_name varchar; BEGIN IF lang IS NOT NULL AND lang = 'lang1' THEN SELECT a.lang1 INTO table1_name FROM table1_lang a WHERE a.id = in_id; IF table1_name IS NOT NULL AND length(table1_name) > 0 THEN RETURN table1_name; ELSE SELECT a.lang1 INTO table2_name FROM table2_lang a WHERE a.table1_id = in_id; IF table2_name IS NOT NULL AND length(table2_name) > 0 THEN RETURN table2_name; END IF; END IF; END IF; IF lang IS NOT NULL AND lang = 'lang2' THEN SELECT a.lang2 INTO table1_name FROM table1_lang a WHERE a.id = in_id; IF table1_name IS NOT NULL AND length(table1_name) > 0 THEN RETURN table1_name; ELSE SELECT a.lang2 INTO table2_name FROM table2_lang a WHERE a.table1_id = in_id; IF table2_name IS NOT NULL AND length(table2_name) > 0 THEN RETURN table2_name; END IF; END IF; END IF; ... IF table2_name IS NULL OR length(table2_name) = 0 THEN RETURN NULL; END IF; END $BODY$ LANGUAGE plpgsql VOLATILE STRICT COST 100; ALTER FUNCTION get_name(integer, text) OWNER TO postgres; But I would like to know if is it possible to create a function in plpgsql or other languages available in PostgreSQL that takes a column as input parameter?
