Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: Basic SQL function error ??

    Hi all
    A have a basic query on db

    Code:
    SELECT *
    FROM users 
    WHERE 
      users.username = $1 
      AND users.password = md5($2)
      AND users.banned = '0'
      AND users.active = '1';
    which returns recordset as planed.
    But when I want to return 2 fields with query

    Code:
    SELECT users.id, users.age
    FROM users 
    WHERE 
      users.username = $1 
      AND users.password = md5($2)
      AND users.banned = '0'
      AND users.active = '1';
    it returns error

    ERROR: return type mismatch in function declared to return users
    DETAIL: Final statement returns bigint instead of character varying at column 2.
    CONTEXT: SQL function "get_users_login"

    what does it has to be set as parameter for "RETURNS"


    this is complete function

    Code:
    CREATE OR REPLACE FUNCTION "public"."get_users_login" ("systemID" varchar, password varchar) RETURNS SETOF "public"."users" AS
    $body$
    SELECT users.id, users.age
    FROM users 
    WHERE 
      users.username = $1 
      AND users.password = md5($2)
      AND users.banned = '0'
      AND users.active = '1';
    $body$
    LANGUAGE 'sql'
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY INVOKER
    COST 100 ROWS 1;

    Thank you in advance

    kind regards
    ddragas

  2. #2
    Join Date
    Aug 2010
    Posts
    2
    *bump*

    anybody ?

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    1) You probably don't have to return a SETOF. It looks like there should only be 1 record returned.

    2) You are returning a record type that matches your users table. The second column is probably something like first_name VARCHAR(50) so it is not happy that you are trying to stuff an int in there. Either fix your return so it matches the structure of the users table. Or change the return type, try two OUT params.

  4. #4
    Join Date
    Nov 2006
    Posts
    82
    You can't declare function to return all columns from users table

    RETURNS SETOF "public"."users"
    and return only the subset of them

    users.id, users.age
    I think that's the problem.
    Instead
    RETURNS SETOF "public"."users"
    You should define a record type (CREATE TYPE ...) and use it as type that function returns

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You can't declare function to return all columns from users table and return only the subset of them
    I believe you can. The missing columns will just be null. The problem is one of type mismatch. OP indicated that it worked when only returning the id (because return value and 1st column were both ints). But when they added an int value in the position of a varchar column, it puked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •