-1

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

  • The error message, if you read it, tells you exactly what the problem is. So, what's your question? – mustaccio Oct 22 '18 at 12:46
2

It seems that question_type is defined as varchar(80) but you as defined question as text.

Simply cast it as text:

select question_type::text

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.