Thomas,
I think this is about the qualification by schema name and scope of the
name of a UDF.
This way you can have the same function name in different schema as
different functions (not only in public schema).
Like:
CREATE ALIAS [schema.]function_name AS ...
Then you can call:
CALL someschema.somefunction( ...);
or
set schema myschema;
CALL myfunction( ...);
The concept behind this is that any referenced object in the function
execution scope it's implicit qualified by function schema (or executing
user's default schema).
Ex.
create alias myschema.test as $$
String test(Connection conn) throws Exception {
ResultSet rs = conn.createStatement().executeQuery("select * from
this_tablename_exist_in_many_schemas " );
...
} $$;
then executed sql would be:
"select * from myschema.this_tablename_exist_in_many_schemas "
Regards,
Dario
El 06/06/10 05:47, Thomas Mueller escribió:
> Hi,
>
> Thanks! I think I understand now. If you are using a different
> connection and set the default schema, you could use the function
> SCHEMA(). If you are using a different user for each schema, you could
> use the functions USER() or CURRENT_USER(). Or you could set a
> variable:
>
> create schema a;
> set schema a;
> set @my_s 'Test';
> drop alias test;
> create alias test as $$
> String test(Connection conn) throws Exception {
> ResultSet rs = conn.createStatement().executeQuery("select schema() ||
> ' ' || @my_s");
> rs.next();
> return rs.getString(1); } $$;
> call test();
>
> But that means each schema needs it's own connection. Does this solve
> the problem?
>
>> I converted existing Oracle database to H2 database as "offline" data
>> processing solution which is syncrhronized from time to time with
>> original database.
>> Data structure must remain the same in H2 and Oracle, because I am
>> using the same programs to access both databases.
>> Database consist of the set of tables which are same in certain number
>> of schemas. Data for company subdivisions are divided in different
>> schemas, and tables in all schemas have the same structure.
>> Also, functions and procedures are the same in different schemas.
>> However, these functions are executing certain SQL statements. If
>> schema is ommited in table name, Oracle is using parent schema (schema
>> of the function), what is not case in H2. My problem is I cannot use
>> single java method as function in multiple schemas (multiple create
>> aliases in different schemas) because I don't know which schema name
>> to use in SQL query inside function.
>>
>> I agree it is not very common case, and not of special importance for
>> H2 development, so I am not expecting solution from your side. I will
>> be satisfied with your tip where to dig inside of H2 code to implement
>> my own patch which will provide schema information in current function
>> in my own H2 version (if it is possible).
>>
>> Best regards,
>> Zvonko
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
