Improving performance with a Function instead of a View

Hi all,
I am using some views now to put together a particular format for my
Java client factory to produce Java Beans from the database.

Because we support internationalisation we are representing values as an
id then storing their multiple languages in unicode to support the same
repesentation at the database.

This format is:

base_table, id bigint, is_disabled boolean default false.

resource_table, foreign_key_to_base_table, locale_foreign_key,
display_name, is_translated

As such, my views are quite slow because there are a number of Right
Joins occuring so that I can present a single "locale" field in the view
that all the localised information will attach to correctly.

That way I can > select * FROM v_object where locale = 'en_GB' and
object_id = 120031;

So if there are three localised joins they are bound to the single
locale.

E.G

create view v_object as
select loc.id as locale,
obj.id as object_id,
obj.user_data as user_data,

type.id as object_type,
type_res.disp_name as object_type_display_name

size.id as object_size,
size_res.disp_name as object_size_disp_name

from locale as loc,
object as obj
left join object_type as type on type.id = obj.object_type
left join object_type_res as type_res on
type_res.object_type = obj.object_type
left join object_size as size on size.id = obj.object_size
left join object_size_res as size_res on
size_res.object_size = obj.object_size
where ( type_res.locale = loc.id OR type_res.locale IS NULL ) AND
( size_res.locale = loc.id OR size_res.locale IS NULL );

In this example the left joins are required to ensure the columns are
returned even if null as not all fields are required.

Anyway, there is a performance problem, and we have a temporary
solution.

I was wondering if it is possible to create a function that will return
a set of data with the correct view names and have this function perform
additional and fast checks server side?

Regards
--
Hadley Willan » Director » hadley(dot)willan(at)deeperdesign(dot)com » +64(21) 28
41 463
Deeper Design Limited » +64(7) 377 3328 » www.deeperdesign.com