Thread: Function nesting issue

i have 2 functions, naming a and b, both outputing a resultset(cursor)
and a integer.
a calls b

a:
CREATE OR REPLACE FUNCTION "public"."t_outer" (out o_rs
"pg_catalog"."refcursor", out o_i integer) RETURNS record AS
...
select t_inner(o_rs, o_i);
...

b:
CREATE OR REPLACE FUNCTION "public"."t_inner" (out o_rs
"pg_catalog"."refcursor", out o_i integer) RETURNS record AS
...

Compilation is ok, but when i call a, it says:
ERROR: function b(refcursor, integer) does not exist
No function matches the given name and argument types. You might need
to add explicit type casts.

So, my question is whether postgreSQL supporting this type of nesting?

thanks. if this is an old question, please forgive me.

regards

--
Alferd.

On 28/01/2010 07:32, 张海峰 wrote:
> i have 2 functions, naming a and b, both outputing a resultset(cursor)
> and a integer.
> a calls b
>
> a:
> CREATE OR REPLACE FUNCTION "public"."t_outer" (out o_rs
> "pg_catalog"."refcursor", out o_i integer) RETURNS record AS
> ...
> select t_inner(o_rs, o_i);
> ...
>
> b:
> CREATE OR REPLACE FUNCTION "public"."t_inner" (out o_rs
> "pg_catalog"."refcursor", out o_i integer) RETURNS record AS
> ...
>
> Compilation is ok, but when i call a, it says:
> ERROR: function b(refcursor, integer) does not exist
> No function matches the given name and argument types. You might need
> to add explicit type casts.

Can you post the query that calls the outer function? In the above,
you've named your functions t_outer() and t_inner(); so if you're trying
to call a function named b(), then naturally you'll get an error.

BTW, if your function names are all lower-case, you don't need all the
double-quotes.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Actually, the real function name is t_outer and t_inner, a and b is
just for convenience.
So you can see them as
CREATE OR REPLACE FUNCTION a (out ...
and
CREATE OR REPLACE FUNCTION b (out ...

And i call function a by jdbc:
...
conn.setAutoCommit(false);
CallableStatement cs = conn.prepareCall("{ call a( ?, ? ) }");
cs.registerOutParameter(1, Types.INTEGER);
cs.registerOutParameter(2, Types.OTHER);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);
...

2010/1/28 Raymond O'Donnell <rod@iol.ie>:
> On 28/01/2010 07:32, 张海峰 wrote:
>> i have 2 functions, naming a and b, both outputing a resultset(cursor)
>> and a integer.
>> a calls b
>>
>> a:
>> CREATE OR REPLACE FUNCTION "public"."t_outer" (out o_rs
>> "pg_catalog"."refcursor", out o_i integer) RETURNS record AS
>> ...
>> select t_inner(o_rs, o_i);
>> ...
>>
>> b:
>> CREATE OR REPLACE FUNCTION "public"."t_inner" (out o_rs
>> "pg_catalog"."refcursor", out o_i integer) RETURNS record AS
>> ...
>>
>> Compilation is ok, but when i call a, it says:
>> ERROR: function b(refcursor, integer) does not exist
>> No function matches the given name and argument types. You might need
>> to add explicit type casts.
>
> Can you post the query that calls the outer function? In the above,
> you've named your functions t_outer() and t_inner(); so if you're trying
> to call a function named b(), then naturally you'll get an error.
>
> BTW, if your function names are all lower-case, you don't need all the
> double-quotes.
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>



--
you are my sunshine, my only sunshine...

=?GB2312?B?1cW6o7fl?= <roxetter@gmail.com> writes:
> And i call function a by jdbc:
> ...
> conn.setAutoCommit(false);
> CallableStatement cs = conn.prepareCall("{ call a( ?, ? ) }");
> cs.registerOutParameter(1, Types.INTEGER);
> cs.registerOutParameter(2, Types.OTHER);
> cs.execute();

That's not the approved syntax for using OUT parameters in Postgres.
It's possible that JDBC would do something under-the-hood to make it
look like it works, but you'd be best off asking on pgsql-jdbc about
whether they do or not.

            regards, tom lane