Is it possible to create a function in PostgreSQL that takes a column name as input?

Question

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?

score 5 · Accepted Answer · edited May 23 '17 at 11:55

5

It is possible in a plpgsql function. But if identifiers (including column names) are parametrized you have to build dynamic SQL and execute it with EXECUTE. Read about that in the manual here. Pay attention not to allow SQL injection by doing that. Make use of quote_ident() or format().

I have written a couple of similar answers with links and explanation. Try this one for a start or this one.

edited May 23 '17 at 11:55

Community

1
1

answered Dec 07 '11 at 09:45

Erwin Brandstetter

605,456
145
1,078
1,228

Is it possible to create a function in PostgreSQL that takes a column name as input?

1 Answers1