Using dynamic column names in PostgreSQL

3 votes

1 answer

29971 views

postgresql stored-procedures plpgsql dynamic-sql postgresql-8.3

                          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?
                        

Asked by user36935 (31 rep)

Apr 10, 2014, 11:08 AM
Last activity: Apr 11, 2014, 12:01 AM

Using dynamic column names in PostgreSQL

Related Questions