Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Multiple out parameters called from function

    Hello all,

    I have a little problem trying to get multiple out parameters from a function called from inside another function...

    I have a first function:
    Code:
    testmultoutfunct(pCode VARCHAR, pRef VARCHAR, OUT pStatus, OUT pId) RETURNS record
    In another function, I'm trying to call this function and retrieve the 2 out parameters to use them
    Code:
    ...
    DECLARE
      vStatus NUMERIC;
      vId VARCHAR(32);
    
    ...
        SELECT testmultoutfunct('TT', '1',) INTO vStatus, vId;
    ...
    But when I execute, I get an error on this line

    ERROR: invalid input syntax for type numeric; "(2,)"

    Those are the actual values returnd by my function in the OUT parameters, but it seems that it tries to affect the whole sting '(2,)' to the first INTO variable that is a numeric...

    I also tried to return an empty string in stead of null in the second parameter, but that doesn't change anything...

    I also tried to declare a variable of type RECORD, but then I don't know how to access the 2 columns in the record.

    Can somebody please help me, I'm really stuck and it doesn't seem that complicated, I must simply be missing something...

    Thanks for your time
    Best regards
    Neil.

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Dont return a record and have out params use one or the other.

    Try this instead:

    Code:
    CREATE OR REPLACE FUNCTION test(
      p_one  IN VARCHAR,
      p_two  IN VARCHAR,
      p_foo  OUT NUMERIC,
      p_bar  OUT NUMERIC
    ) AS ...

  3. #3
    Join Date
    Aug 2009
    Posts
    3
    Hi Artacus!

    Thanks for your response, but it doesn't change anything with or without the 'RETURNS record', in fact, if I don't specify it, when I look at the function in the database (through pgAdmin III), it automatically adds the 'RETURNS record' statement.

    What am I doing wrong?
    Is it right to use the SELECT INTO in the other function to get the out parameters?

    Thanks for your time
    Best regards
    Neil.
    Last edited by doczorg; 09-01-09 at 04:26.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Do this:

    Code:
    SELECT pStatus, pId INTO vStatus, vId
    FROM testmultoutfunct('TT', '1')

  5. #5
    Join Date
    Aug 2009
    Posts
    3
    Thanks Artacus, it works perfect!
    Have a nice day
    Neil.

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You bet. I just saw this trick and thought I'd share:

    Code:
    SELECT (testmultoutfunct('TT','1')).* INTO vStatus, vId
    This isn't a feature I use too often, but it is handy when you've got complex calculations and need to return x,y coordinates or min, max etc. But if you want to see a bunch of people get their panties in knot, ask this question on an Oracle forum. LOL.

    I'm not sure why they get offended. Maybe they're upset their expensive db can't do something that a free db does.

Posting Permissions

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