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?