473,325 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

ERROR: return type mismatch in function returning tuple

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
1 11689
michaelb
534 Expert 512MB
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

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

Similar topics

1
by: Jon Perez | last post by:
I want to retrieve a value from a tuple and convert it to a C type. Is the following idiom okay? if (!( ( tmp_pyobj=PyTuple_GetItem(tuple,1) ) && ( c_int=PyInt_AsLong(tmp_pyobj) ) )) { if...
66
by: Darren Dale | last post by:
Hello, def test(data): i = ? This is the line I have trouble with if i==1: return data else: return data a,b,c,d = test()
5
by: Neal Coombes | last post by:
Posted to comp.lang.c++.moderated with little response. Hoping for better from the unmoderated groups: -------- Original Message -------- Subject: Return appropriately by value, (smart)...
4
by: leslie_tighe | last post by:
Hello, I have a method on a com+ object that is returning an array of objects. I know the array is popluated as calls to check the ubound and lbound show valid values. However, any calls to...
10
by: ale.of.ginger | last post by:
Greetings! I am trying to make a multiplayer (no AI, 2 person) game of tic tac toe in Python. So far it has been pretty simple. My only concern is with the win checking to see if a person has...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
9
by: Prasad | last post by:
HI, I am a beginner in VC++.. I am trying to write a Win32 console application in visual studio.. I am using following header files.. #include <STRING> using namespace std; #include...
3
by: Oliver Bleckmann | last post by:
hey, which ways are available i c++ to return multiple values i know u can use structs, any other methods? concrete question: i want to do sth. like this: returning two strings into stringvars...
9
by: Jim | last post by:
Hello, I'm trying to write exception-handling code that is OK in the presence of unicode error messages. I seem to have gotten all mixed up and I'd appreciate any un-mixing that anyone can...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.