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
