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
?
Best Answer
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:
Call:
Here, I am simply using a column alias in the call to achieve what you want. Much easier than dynamic column names ...