Performance question (FOR loop)

Started by vishal saberwalover 20 years ago2 messagesgeneral
Jump to latest
#1vishal saberwal
vishalsaberwal@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: vishal saberwal (#1)
Re: Performance question (FOR loop)

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