By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

ERROR: return type mismatch in function returning tuple

P: 66
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION f_customerlogininfo(varchar, varchar, varchar) RETURNS public.v_customerlogininfo AS '
  2. DECLARE
  3.     p_schm ALIAS FOR $1;
  4.     p_contact ALIAS FOR $2;
  5.     p_status ALIAS FOR $3;
  6.     v_boolstatus boolean;
  7. BEGIN
  8.     IF p_status = ''all'' THEN
  9.         RETURN (select * from v_customerlogininfo where contact = p_contact);
  10.     ELSE
  11.         IF p_status = ''true'' THEN
  12.             v_boolstatus := true;
  13.         ELSIF p_status = ''false'' THEN
  14.             v_boolstatus := false;
  15.         END IF;
  16.             RETURN (select * from v_customerlogininfo where contact = p_contact and status = v_boolstatus);
  17.     END IF;
  18. END; '
  19. LANGUAGE 'plpgsql'
Expand|Select|Wrap|Line Numbers
  1. select f_customerlogininfo ('Iwachi Miura', '09012345678', 'all');
ERROR: return type mismatch in function returning tuple at or near "(";
Aug 13 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 534
The first problem with your code is that such function must have return type as SETOF TABLE_NAME, not TABLE_NAME
I think the second problem is with your "return (select ...)" code.
plpgsql function would typically do something like this:
Expand|Select|Wrap|Line Numbers
  1. create or replace function getSetOf (int, int) returns setof tab_name as
  2. '
  3.  declare
  4.     -- either one will do
  5.     ret_row record;
  6.     -- ret_row tab_name%rowtype;
  7.     start_id alias for $1;
  8.     end_id   alias for $2;
  9.  
  10.   begin
  11.      FOR ret_row in select * from tab_name 
  12.              where tab_name.id between start_id and end_id LOOP
  13.         return next ret_row;
  14.      END LOOP;
  15.  
  16.   return;
  17.   end;
  18. '
  19. language 'plpgsql';
  20.  
Review these docs for more information:
http://www.postgresql.org/docs/8.1/s...efunction.html
http://www.postgresql.org/docs/8.1/s...tructures.html
http://www.postgresql.org/docs/8.1/s...larations.html
http://www.postgresql.org/docs/8.1/s...xfunc-sql.html
Aug 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.