Access table columns dynamically-CodePudding

i would like to create a function to get table columns dynamically, what I mean by that

In js, we access objects by their key: object["keyName"]. Which goes directly to the value I want.

Basically my use case is that I have a series of localizations on my database, separated by the slug label_country i.e: label_en, label_es etc.

How could I create a function to select for label_{someString} and return that column?

Thanks!

CodePudding user response：

Select the whole row as JSON and then extract the column by its name. Here is an illustration.

create or replace function col_by_name(colname text, arg_id integer)
returns text language sql stable as
$$
 select (to_json(t)) ->> ('label_'||colname) from the_table t where id = arg_id
$$;

SQL Fiddle here.