FAQ
Hi,

I'm having difficulty working out the correct syntax to return more than one
value from a stored procedure. I wish to return an INTGER and a string

CREATE or REPLACE FUNCTION testproc(val1 (CHAR9), val2 CHAR(4)) RETURNS
INTEGER, CHAR(640) AS $$

The above is incorrect but what is the correct syntax?

Thanks

Atif

Search Discussions

  • Andreas Kretschmer at Jul 8, 2010 at 10:14 am

    In response to Atif Jung :
    Hi,

    I'm having difficulty working out the correct syntax to return more than one
    value from a stored procedure. I wish to return an INTGER and a string

    CREATE or REPLACE FUNCTION testproc(val1 (CHAR9), val2 CHAR(4)) RETURNS
    INTEGER, CHAR(640) AS $$

    The above is incorrect but what is the correct syntax?
    You should use IN/OUT - Parameters, foro example:

    test=# create or replace function atif(in int, in text, out a int, out b text) returns record as $$begin a=1; b='hello world'; return; end;$$language plpgsql;
    CREATE FUNCTION
    test=*# select * from atif(0,'test');
    a | b
    ---+-------------
    1 | hello world
    (1 row)


    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
    GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
  • Atif Jung at Jul 8, 2010 at 10:21 am
    Thanks Andreas but is there no alternative?

    I'm porting from an INFORMIX database which allows the return of more than
    one value from a stored procedure without having to use in/out
    parameters. The procedure is called from several places across the system
    and will be time consuming to change all instances to include the new out
    parameters. To be able to return more than one parameter will be of great
    help to me, if it's possible.

    Many Thanks

    Atif



    On 8 July 2010 11:14, A. Kretschmer wrote:

    In response to Atif Jung :
    Hi,

    I'm having difficulty working out the correct syntax to return more than one
    value from a stored procedure. I wish to return an INTGER and a string

    CREATE or REPLACE FUNCTION testproc(val1 (CHAR9), val2 CHAR(4)) RETURNS
    INTEGER, CHAR(640) AS $$

    The above is incorrect but what is the correct syntax?
    You should use IN/OUT - Parameters, foro example:

    test=# create or replace function atif(in int, in text, out a int, out b
    text) returns record as $$begin a=1; b='hello world'; return; end;$$language
    plpgsql;
    CREATE FUNCTION
    test=*# select * from atif(0,'test');
    a | b
    ---+-------------
    1 | hello world
    (1 row)


    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
    GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice
  • Tim Landscheidt at Jul 8, 2010 at 10:50 am

    Atif Jung wrote:

    Thanks Andreas but is there no alternative?
    I'm porting from an INFORMIX database which allows the return of more than
    one value from a stored procedure without having to use in/out
    parameters. The procedure is called from several places across the system
    and will be time consuming to change all instances to include the new out
    parameters. To be able to return more than one parameter will be of great
    help to me, if it's possible.
    [...]
    Andreas' example returns more than one value. Perhaps you
    could clarify where your problem lies.

    Tim
  • Andreas Kretschmer at Jul 8, 2010 at 11:13 am

    In response to Atif Jung :
    Thanks Andreas but is there no alternative?

    I'm porting from an INFORMIX database which allows the return of more than one
    value from a stored procedure without having to use in/out parameters. The
    procedure is called from several places across the system and will be time
    consuming to change all instances to include the new out parameters. To be able
    to return more than one parameter will be of great help to me, if it's
    possible.
    Other solution, but new problem for you:

    test=# create or replace function atif(int, text) returns record as
    $$declare r record; begin select into r 1::int, 'hello world'::text; return r; end;$$language plpgsql;
    CREATE FUNCTION
    test=*# select * from atif (0,'test') as foo(a int, b text);
    a | b
    ---+-------------
    1 | hello world
    (1 row)

    test=*# select * from atif (0,'test');
    ERROR: a column definition list is required for functions returning "record"
    LINE 1: select * from atif (0,'test');


    Now you have to define the result-type later, but you have to define
    that.

    Regards, Andreas
    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
    GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
  • Thomas Kellerer at Jul 8, 2010 at 11:44 am

    Atif Jung, 08.07.2010 11:51:
    Hi,
    I'm having difficulty working out the correct syntax to return more than
    one value from a stored procedure. I wish to return an INTGER and a string
    CREATE or REPLACE FUNCTION testproc(val1 (CHAR9), val2 CHAR(4)) RETURNS
    INTEGER, CHAR(640) AS $$
    The above is incorrect but what is the correct syntax?
    Thanks

    Atif
    If you don't need the power of pl/pgSQL to calculate your result, a simple SQL function should work:

    CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
    RETURNS TABLE(id integer, some_value text)
    AS
    $$
    SELECT 42, 'your value'::text;
    $$
    LANGUAGE sql;
  • Thomas Kellerer at Jul 8, 2010 at 11:54 am

    Thomas Kellerer, 08.07.2010 13:43:
    Atif Jung, 08.07.2010 11:51:
    Hi,
    I'm having difficulty working out the correct syntax to return more than
    one value from a stored procedure. I wish to return an INTGER and a
    string
    CREATE or REPLACE FUNCTION testproc(val1 (CHAR9), val2 CHAR(4)) RETURNS
    INTEGER, CHAR(640) AS $$
    The above is incorrect but what is the correct syntax?
    Thanks

    Atif
    If you don't need the power of pl/pgSQL to calculate your result, a
    simple SQL function should work:

    CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
    RETURNS TABLE(id integer, some_value text)
    AS
    $$
    SELECT 42, 'your value'::text;
    $$
    LANGUAGE sql;
    I did hit "Send" too quickly...

    If you do need calculations in there (and thus the power of PL/pgSQL), you can do that as well:

    CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
    RETURNS TABLE(id integer, some_value text)
    AS
    $$
    DECLARE
    return_int integer;
    return_text text;
    BEGIN
    return_int := 21 * 2;
    return_text := 'Your input value: ' || val1;

    RETURN QUERY SELECT return_int, return_text;
    END
    $$
    LANGUAGE plpgsql;

    Both can be used like this: select * from testproc('x', 'y')
  • Atif Jung at Jul 8, 2010 at 1:33 pm
    Thanks Thomas et al,

    your advice and pointer have been of great help. to minimise my code change
    I've decide to use the RETURNS TABLE option, but I now have the following
    problem.

    My procedure is as follows:

    CREATE or REPLACE FUNCTION myproc(var1 CHAR(9), var2 (CHAR4)) RETURNS TABLE
    (result INTEGER, reply TEXT)) AS $$
    DECLARE
    replyx TEXT;
    result INTEGER;
    BEGIN
    replyx := 'HELLO WORLD';
    result := 150

    RETURN QUERY SELECT result, replyx;
    END;
    $$ LANGUAGE plpgsql;

    I call it from ECPG (EMBEDDED SQL IN C) as follows:

    EXEC SQL BEGIN DECLARE SECTION;
    int iResult
    char acReply[1000];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL SELECT myproc ('abcdefghi', 'test') INTO :iResult, :acReply;

    The error I get when I run the code is:

    SQLSTATE: 42804
    ERROR MESSAGE: invalid input syntax for type int: "(150,"HELLO

    What am I doing wrong?

    Many Thanks

    Atif



    On 8 July 2010 12:53, Thomas Kellerer wrote:

    Thomas Kellerer, 08.07.2010 13:43:

    Atif Jung, 08.07.2010 11:51:
    Hi,
    I'm having difficulty working out the correct syntax to return more than
    one value from a stored procedure. I wish to return an INTGER and a
    string
    CREATE or REPLACE FUNCTION testproc(val1 (CHAR9), val2 CHAR(4)) RETURNS
    INTEGER, CHAR(640) AS $$
    The above is incorrect but what is the correct syntax?
    Thanks

    Atif

    If you don't need the power of pl/pgSQL to calculate your result, a
    simple SQL function should work:

    CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
    RETURNS TABLE(id integer, some_value text)
    AS
    $$
    SELECT 42, 'your value'::text;
    $$
    LANGUAGE sql;
    I did hit "Send" too quickly...

    If you do need calculations in there (and thus the power of PL/pgSQL), you
    can do that as well:


    CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
    RETURNS TABLE(id integer, some_value text)
    AS
    $$
    DECLARE
    return_int integer;
    return_text text;
    BEGIN
    return_int := 21 * 2;
    return_text := 'Your input value: ' || val1;
    RETURN QUERY SELECT return_int, return_text;
    END
    $$
    LANGUAGE plpgsql;

    Both can be used like this: select * from testproc('x', 'y')





    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice
  • Atif Jung at Jul 8, 2010 at 1:53 pm
    Thomas,

    I figured out what the problem was, the function call returns a string, not
    as I initially thought an INT and a string.

    Kind Rgds

    Atif



    On 8 July 2010 14:33, Atif Jung wrote:

    Thanks Thomas et al,

    your advice and pointer have been of great help. to minimise my code change
    I've decide to use the RETURNS TABLE option, but I now have the following
    problem.

    My procedure is as follows:

    CREATE or REPLACE FUNCTION myproc(var1 CHAR(9), var2 (CHAR4)) RETURNS TABLE
    (result INTEGER, reply TEXT)) AS $$
    DECLARE
    replyx TEXT;
    result INTEGER;
    BEGIN
    replyx := 'HELLO WORLD';
    result := 150

    RETURN QUERY SELECT result, replyx;
    END;
    $$ LANGUAGE plpgsql;

    I call it from ECPG (EMBEDDED SQL IN C) as follows:

    EXEC SQL BEGIN DECLARE SECTION;
    int iResult
    char acReply[1000];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL SELECT myproc ('abcdefghi', 'test') INTO :iResult, :acReply;

    The error I get when I run the code is:

    SQLSTATE: 42804
    ERROR MESSAGE: invalid input syntax for type int: "(150,"HELLO

    What am I doing wrong?

    Many Thanks

    Atif



    On 8 July 2010 12:53, Thomas Kellerer wrote:

    Thomas Kellerer, 08.07.2010 13:43:

    Atif Jung, 08.07.2010 11:51:
    Hi,
    I'm having difficulty working out the correct syntax to return more than
    one value from a stored procedure. I wish to return an INTGER and a
    string
    CREATE or REPLACE FUNCTION testproc(val1 (CHAR9), val2 CHAR(4)) RETURNS
    INTEGER, CHAR(640) AS $$
    The above is incorrect but what is the correct syntax?
    Thanks

    Atif

    If you don't need the power of pl/pgSQL to calculate your result, a
    simple SQL function should work:

    CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
    RETURNS TABLE(id integer, some_value text)
    AS
    $$
    SELECT 42, 'your value'::text;
    $$
    LANGUAGE sql;
    I did hit "Send" too quickly...

    If you do need calculations in there (and thus the power of PL/pgSQL), you
    can do that as well:


    CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
    RETURNS TABLE(id integer, some_value text)
    AS
    $$
    DECLARE
    return_int integer;
    return_text text;
    BEGIN
    return_int := 21 * 2;
    return_text := 'Your input value: ' || val1;
    RETURN QUERY SELECT return_int, return_text;
    END
    $$
    LANGUAGE plpgsql;

    Both can be used like this: select * from testproc('x', 'y')





    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 8, '10 at 9:51a
activeJul 8, '10 at 1:53p
posts9
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase