1

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?

  • It's missing a semicolon after RETURN QUERY EXECUTE sql – Quandary Sep 12 at 9:29
5

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 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.