FAQ
I'm having some difficulty getting plpgsql to recognize a function with
a couple of OUT parameters. I'm either declaring the function
incorrectly, making the call to it in the wrong way or my program is
simply possessed by evil spirits. I'm using Postgres 8.1.5.
What appears to be happening is that it's declaring the function as if
it returned a record and had only two parameters, but I'm trying to
call it with four parameters, with two of them being OUT parameters. So
the compiler sees two different versions of the function and refused to
do anything more. The example below shows the problem, but it's just
something to exercise the function calls and generate the error. Can
anyone spot the screw-up in this little example? (the error message is
listed below in the block comment)
TIA,
-Bill Thoen

CREATE OR REPLACE FUNCTION fishy( s1 text, s2 text, OUT n integer, OUT f
real ) AS $$
DECLARE
c integer;
BEGIN
c := length( s1 );
n := length( s1 || s2 );
f := c::real / n::real;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION main() RETURNS VOID AS $$
DECLARE
str1 text;
str2 text;
num integer := 0;
fnum real := 0.0;
BEGIN
str1 := 'One fish, two fish';
str2 := 'Shark fish, No fish';
SELECT fishy( str1, str2, num, fnum) ;

RAISE NOTICE 'fishy() analysis: % %', num, fnum;
END;
$$ LANGUAGE plpgsql;

SELECT main();

/* ERROR MESSAGE

psql:ex_out_fail.sql:28: ERROR: function fishy(text, text, integer,
real) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
CONTEXT: SQL statement "SELECT fishy( $1 , $2 , $3 , $4 )"
PL/pgSQL function "main" line 9 at SQL statement

And when I run \df from the pgsql command line, it shows up like this:
fishy | record | text, text
*/

Search Discussions

  • Pavel Stehule at Jul 14, 2010 at 6:41 pm
    Hello

    PostgreSQL use OUT params very untypically. You can't to directly to
    join OUT parameter with some variable. It isn't possible.

    please, try

    CREATE OR REPLACE FUNCTION foo(a int, b int, OUT c int, OUT d int)
    RETURNS record AS $$
    BEGIN
    c := a + 1;
    d := b + 1;
    RETURN;
    END;
    $$ LANGUAGE plpgsql strict immutable;

    CREATE OR REPLACE FUNCTION use_foo()
    RETURNS void AS $$
    DECLARE r record AS $$
    BEGIN
    r := foo(10,20);
    RAISE NOTICE '% %', r.c, r.d;
    END;
    $$ LANGUAGE plpgsql immutable;

    SELECT use_foo();

    Regard

    Pavel Stehule

    2010/7/14 Bill Thoen <bthoen@gisnet.com>:
    I'm having some difficulty getting plpgsql to recognize a function with a
    couple of OUT parameters. I'm either declaring the function incorrectly,
    making the call to it in the wrong way or my program is simply possessed by
    evil spirits. I'm using Postgres 8.1.5.
    What appears  to be happening is that it's declaring the function as if it
    returned  a record and had only two  parameters, but I'm trying to call it
    with four parameters, with two of them being OUT parameters. So the compiler
    sees two different versions of the function and refused to do anything more.
    The example below shows the problem, but it's just something to exercise the
    function calls and generate the error. Can anyone spot the screw-up in this
    little example? (the error message is listed below in the block comment)
    TIA,
    -Bill Thoen

    CREATE OR REPLACE FUNCTION fishy( s1 text, s2 text, OUT n integer, OUT f
    real ) AS $$
    DECLARE
    c integer;
    BEGIN
    c := length( s1 );
    n := length( s1 || s2 );
    f  := c::real / n::real;
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION main() RETURNS VOID AS $$
    DECLARE
    str1 text;
    str2 text;
    num integer := 0;
    fnum real := 0.0;
    BEGIN
    str1 := 'One fish, two fish';
    str2 := 'Shark fish, No fish';
    SELECT fishy( str1, str2, num, fnum) ;

    RAISE NOTICE 'fishy() analysis: %  %', num, fnum;
    END;
    $$ LANGUAGE plpgsql;

    SELECT main();

    /*  ERROR MESSAGE

    psql:ex_out_fail.sql:28: ERROR:  function fishy(text, text, integer, real)
    does not exist
    HINT:  No function matches the given name and argument types. You may need
    to add explicit type casts.
    CONTEXT:  SQL statement "SELECT  fishy(  $1 ,  $2 ,  $3 ,  $4 )"
    PL/pgSQL function "main" line 9 at SQL statement

    And when I run \df from the pgsql command line, it shows up like this:
    fishy            | record                | text, text
    */


    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
  • Joshua D. Drake at Jul 14, 2010 at 6:52 pm

    On Wed, 2010-07-14 at 12:21 -0600, Bill Thoen wrote:
    I'm having some difficulty getting plpgsql to recognize a function with
    a couple of OUT parameters. I'm either declaring the function
    incorrectly, making the call to it in the wrong way or my program is
    simply possessed by evil spirits. I'm using Postgres 8.1.5.
    First, 8.1.x is EOL as of November. You need to upgrade.

    CREATE OR REPLACE FUNCTION main() RETURNS VOID AS $$
    DECLARE
    str1 text;
    str2 text;
    num integer := 0;
    fnum real := 0.0;
    BEGIN
    str1 := 'One fish, two fish';
    str2 := 'Shark fish, No fish';
    SELECT fishy( str1, str2, num, fnum) ;
    /* ERROR MESSAGE

    psql:ex_out_fail.sql:28: ERROR: function fishy(text, text, integer,
    real) does not exist
    HINT: No function matches the given name and argument types. You may
    need to add explicit type casts.
    CONTEXT: SQL statement "SELECT fishy( $1 , $2 , $3 , $4 )"
    PL/pgSQL function "main" line 9 at SQL statement
    You are passing four IN paramaters. The out paramaters are used in
    return are they not?

    Joshua D. Drake


    --
    PostgreSQL.org Major Contributor
    Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
    Consulting, Training, Support, Custom Development, Engineering
  • Tom Lane at Jul 14, 2010 at 7:16 pm

    "Joshua D. Drake" <jd@commandprompt.com> writes:
    On Wed, 2010-07-14 at 12:21 -0600, Bill Thoen wrote:
    I'm having some difficulty getting plpgsql to recognize a function with
    a couple of OUT parameters.
    psql:ex_out_fail.sql:28: ERROR: function fishy(text, text, integer,
    real) does not exist
    You are passing four IN paramaters. The out paramaters are used in
    return are they not?
    You'd need to do something like

    SELECT * INTO num, fnum FROM fishy(str1, str2) ;

    OUT parameters in PG are just syntactic sugar for defining a
    record-returning function. The call syntax still works as if you'd
    written CREATE FUNCTION foo (IN-parameters-only) RETURNS some-record-type.

    regards, tom lane
  • Bill Thoen at Jul 14, 2010 at 8:07 pm
    Thanks guys. I think I see now. I was thinking it was a more transparent
    pass-by-value / pass-by-reference thing.
    Anyway I solved my problem by going back into my comfort zone and
    explicitly return a record and I'm not using OUT parameters. They're
    aren't what I thought they were and I'm working on a tight schedule, so
    I don't have much toim m eto explre. The compiler seems happier without
    them, and when it's happy, I'm happy.

    Tom Lane wrote:
    "Joshua D. Drake" <jd@commandprompt.com> writes:
    On Wed, 2010-07-14 at 12:21 -0600, Bill Thoen wrote:

    I'm having some difficulty getting plpgsql to recognize a function with
    a couple of OUT parameters.
    psql:ex_out_fail.sql:28: ERROR: function fishy(text, text, integer,
    real) does not exist
    You are passing four IN paramaters. The out paramaters are used in
    return are they not?
    You'd need to do something like

    SELECT * INTO num, fnum FROM fishy(str1, str2) ;

    OUT parameters in PG are just syntactic sugar for defining a
    record-returning function. The call syntax still works as if you'd
    written CREATE FUNCTION foo (IN-parameters-only) RETURNS some-record-type.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 14, '10 at 6:10p
activeJul 14, '10 at 8:07p
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase