I want to create a basic function which takes an input and throws a table with multiple columns and rows as an output . I have written the following query for the same :
create or replace function proc_name_1 (store text)
returns table (question text , resp numeric , gendr text , resp_count integer)
as $$
begin
return query
select question_type , response, gender, sum(response_count)
from fashtagcompute.response_count
where store_id = store
group by question_type , response, gender ;
end ;
$$
language plpgsql ;
CREATE FUNCTION
The function is succesfully created. However if I try and execute it , I get the following error
select * from proc_name_1('ab1f47ff-5c96-46fb-b975-81bf92c01b63')
ERROR: structure of query does not match function result type
DETAIL: Returned type character varying(80) does not match expected type text in column 1.
CONTEXT: PL/pgSQL function proc_name_1(text) line 3 at RETURN QUERY
Please help.
Thanks
Best Answer
It seems that
question_type
is defined as varchar(80) but you as definedquestion
as text.Simply cast it as text: