Using dynamic column names in PostgreSQL

Question

I am using PostgreSSQL 8.3.

I want to execute this query:

select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc';

Twist in the story is column name colum_fr should be generated dynamically. my_table has different columns for different languages. Something like:

id col col_ja col_fr

I am using this query in crystal reports where I can pass string parameter for language:

select COALESCE(col_||{?parameter},col,'Unknown') from my_table where id = 'abc';

which will internally be converted to something like the following, if {?language} value is fr:

select COALESCE(col_||'fr',col,'Unknown') from my_table where id = 'abc';

which will never work.

I don't want to use select case, to make it dynamic.
As an alternative solution I also tried to create a stored procedure:

CREATE OR REPLACE FUNCTION get_policy_name (
 id text,
 lang text,
 def_value text
)
RETURNS SETOF record AS
$body$
DECLARE
 sql text;
BEGIN
 sql := 'SELECT COALESCE(col_'||quote_ident(lang)||',col,'||quote_literal(def_value)||')FROM my_table WHERE id ='||quote_literal(id);
 RETURN QUERY EXECUTE sql
END;
$body$ LANGUAGE plpgsql;

Which should return single record.
It is not working. What am I missing? Does PostgreSSQL 8.3 support RETURN QUERY EXECUTE?

• Quandary: It's missing a semicolon after RETURN QUERY EXECUTE sql

Answer 1

RETURN QUERY EXECUTE was introduced with Postgres 8.4.
Your version is just too old and unsupported by now. Upgrade to a more recent version.

Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front.

Returning anonymous records without a column definition list only works for a single record. Else you have to provide a column definition list in the call. Details under his question on SO:
Return multiple fields as a record in PostgreSQL with PL/pgSQL

There are limited ways around this with with polymorphic types. Advanced stuff:
Refactor a PL/pgSQL function to return the output of various SELECT queries

BTW, your function would look like this in modern PL/pgSQL:

CREATE OR REPLACE FUNCTION get_policy_name (
 _id int,
 _lang text,
 _def_value text
) RETURNS TABLE (col text) AS
$func$
BEGIN
 RETURN QUERY EXECUTE
 format('SELECT COALESCE(%I, col, 1ドル)
 FROM my_table WHERE id = 2ドル'
 , 'col_' || _lang)
 USING _def_value, _id;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT col AS col_fr FROM get_policy_name (1, 'fr', 'foo');

Here, I am simply using a column alias in the call to achieve what you want. Much easier than dynamic column names ...