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: