Thread: How to callproc a PL/pgSQL function a cursor OUT parameter and two OUT parameters
How to callproc a PL/pgSQL function a cursor OUT parameter and two OUT parameters
From
Néstor Boscán
Date:
Hi
I have a PL/pgSQL that looks like this:
create or replace function my_function (p_cursor out refcursor, p_code out varchar, p_message out varchar) as $$
begin
open p_cursor for
select * from table;
p_code := 'AJ001';
return;
end; $$ language plpgsql;
I've tried to invoke this function using:
cursor.callproc('schema.my_function', [ 'mycursor', code, message ])
But I get:
HINT: Ninguna función coincide en el nombre y tipos de argumentos. Puede ser necesario agregar conversión explícita de tipos.
In english is something like No function matches the name and types of arguments.
Any ideas?
Regards,
Néstor
Re: How to callproc a PL/pgSQL function a cursor OUT parameter and two OUT parameters
From
Adrian Klaver
Date:
On 10/04/2014 09:16 AM, Néstor Boscán wrote: > Hi > > I have a PL/pgSQL that looks like this: > > create or replace function my_function (p_cursor out refcursor, p_code > out varchar, p_message out varchar) as $$ > begin > open p_cursor for > select * from table; > p_code := 'AJ001'; > > return; > end; $$ language plpgsql; > > I've tried to invoke this function using: > > cursor.callproc('schema.my_function', [ 'mycursor', code, message ]) > > But I get: > > HINT: Ninguna funci├│n coincide en el nombre y tipos de argumentos. > Puede ser necesario agregar conversi├│n expl├¡cita de tipos. > > In english is something like No function matches the name and types of > arguments. > > Any ideas? http://www.postgresql.org/docs/9.3/interactive/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS "Notice that output parameters are not included in the calling argument list when invoking such a function from SQL. This is because PostgreSQL considers only the input parameters to define the function's calling signature. That means also that only the input parameters matter when referencing the function for purposes such as dropping it. We could drop the above function with either of.." So drop the OUT variables from your function call. > > Regards, > > Néstor -- Adrian Klaver adrian.klaver@aklaver.com
Re: How to callproc a PL/pgSQL function a cursor OUT parameter and two OUT parameters
From
Adrian Klaver
Date:
On 10/04/2014 10:18 AM, Néstor Boscán wrote: CCing list. > So in PL/pgSQL I can't have a function with a cursor and out parameters? I don't know I have not tried. Whether it works or not is not related to the error message you are getting. OUT parameters are for output only, they are declared in the function creation but not used when calling the function. The fact that you are passing them in as arguments(including the refcursor name) when calling the function is what is causing the error. The way you are calling the function makes Postgres look for a function with the signature of something like: my_function(text, text, text) when in reality your function has the signature of: my_function() There is no version of my_function that has the first signature so you get the error. The section I linked to has examples showing how to work with OUT parameters. The examples use SQL functions, but the same thing applies to plpgsl functions. > > Regards, > > Néstor > > On Sat, Oct 4, 2014 at 12:21 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 10/04/2014 09:16 AM, Néstor Boscán wrote: > > Hi > > I have a PL/pgSQL that looks like this: > > create or replace function my_function (p_cursor out refcursor, > p_code out varchar, p_message out varchar) as $$ > begin > open p_cursor for > select * from table; > p_code := 'AJ001'; > > return; > end; $$ language plpgsql; > > I've tried to invoke this function using: > > cursor.callproc('schema.my___function', [ 'mycursor', code, > message ]) > > But I get: > > HINT: Ninguna funci├│n coincide en el nombre y tipos de > argumentos. Puede ser necesario agregar conversi├│n expl├¡cita > de tipos. > > In english is something like No function matches the name and > types of arguments. > > Any ideas? > > > http://www.postgresql.org/__docs/9.3/interactive/xfunc-__sql.html#XFUNC-OUTPUT-__PARAMETERS > <http://www.postgresql.org/docs/9.3/interactive/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS> > > "Notice that output parameters are not included in the calling > argument list when invoking such a function from SQL. This is > because PostgreSQL considers only the input parameters to define the > function's calling signature. That means also that only the input > parameters matter when referencing the function for purposes such as > dropping it. We could drop the above function with either of.." > > > So drop the OUT variables from your function call. > > > Regards, > > Néstor > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com