Performance question (FOR loop)

From: vishal saberwal <vishalsaberwal(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Performance question (FOR loop)
Date: 2005-09-01 22:17:05
Message-ID: 3e74dc25050901151722e6d185@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-09-01 22:28:46 Re: PL/pgSQL: EXCEPTION NOSAVEPOINT
Previous Message Matt Miller 2005-09-01 22:14:55 Re: PL/pgSQL: EXCEPTION NOSAVEPOINT