Postgresql – Structure of query does not match function result type, RETURNS TABLE

postgresql

I need a simple function to return dynamic set of columns. I've found couple of examples on SO and end up with the following:

CREATE or replace FUNCTION getColumn(_column1 text, _column2 text, _column3 text, _table text)
  RETURNS TABLE(cmf1 text, cmf2 text, cmf3 text) AS $$
BEGIN
    RETURN QUERY EXECUTE 
        'SELECT ' 
            || quote_ident(_column1)::text || ' as cmf1,'
            || quote_ident(_column2)::text || ' as cmf2,'
            || quote_ident(_column3)::text || ' as cmf3'
        ' FROM '
            || quote_ident(_table); 
END;
 $$ LANGUAGE plpgsql;

I need this function to work only with varchar/text columns so I created this testing table:

create table test20130205 (
    a text,
    b text,
    c varchar,
    d text)
;

Finally, I can run some tests:

select * from getColumn('a','b','d','test20130205');
-- ok
select * from getColumn('a','b','c','test20130205');
-- error
ERROR:  structure of query does not match function result type
DETAIL:  Returned type character varying does not match expected type text in column 3.
CONTEXT:  PL/pgSQL function getcolumn(text,text,text,text) line 3 at RETURN QUERY

It seems like type for column c (varchar) is checked before cast – this seems strange, but I guess I've missed something.

How can I fix my function?

(PostgreSQL 9.1)

Best Answer

In your current function, the casts to text do not apply to the output columns values, they apply to their names (the result of quote_ident).

The cast should be moved inside the query itself:

CREATE or replace FUNCTION getColumn(_column1 text, _column2 text, _column3 text, _table text)
  RETURNS TABLE(cmf1 text, cmf2 text, cmf3 text) AS $$
BEGIN
    RETURN QUERY EXECUTE 
        'SELECT ' 
            || quote_ident(_column1) || '::text as cmf1,'
            || quote_ident(_column2) || '::text as cmf2,'
            || quote_ident(_column3) || '::text as cmf3'
        ' FROM '
            || quote_ident(_table); 
END;
 $$ LANGUAGE plpgsql;
Related Topic