Обсуждение: Performance question (FOR loop)

hi,
I have this preformance question.

create view test_v as select 'text'::varchar as Field1, 'text'::varchar as Field2;

create or replace function test()  returns setof test_v as $$
declare
   res test_v%ROWTYPE;
begin
  for res in
    select t1.field1, t1.field2  from table1 t1;
 loop
 return next res;
end loop;
return;
end;
$$ Language plpgsql;

where table1 has fields other than field1 and field2.

I can run this query at the prompt, but i do not want the aplication layer to know my database schema.
The only way i know I can hide the database architecture is giving 'em the stored procedure name to call (in this case: test() ).

The query I am actually trying to optimize is long and has a few joins (for normalization) and hence didn't copy it here.
The function structure is similar to the one above.

(a) Am i right in thinking that if I eliminate the for loop, some performance gain can be achieved?
(b) Is there a way to eliminate this for loop?
(c) Is there any other way anyone has implemented where they have Application layer API accessing the database
with its schema hidden?

thanks,
vish

vishal saberwal <vishalsaberwal@gmail.com> writes:
> The query I am actually trying to optimize is long and has a few joins (for=
> =20
> normalization) and hence didn't copy it here.
> The function structure is similar to the one above.

> (a) Am i right in thinking that if I eliminate the for loop, some=20
> performance gain can be achieved?
> (b) Is there a way to eliminate this for loop?

Is the plpgsql layer actually doing anything useful, or just passing
back the results of the SQL command?  If the latter, use a SQL function
instead.  Or perhaps even better, replace the function by a view.

            regards, tom lane