Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function

Поиск
Список
Период
Сортировка
От Gary Chambers
Тема Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function
Дата
Msg-id 302670f20910161927h688e13e7qd096e63e68b7ae6c@mail.gmail.com
обсуждение исходный текст
Ответы Re: Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function  (Rob Sargent <robjsargent@gmail.com>)
Список pgsql-sql
All...

In the poly_example function below, I am emulating an actual
requirement by querying the same table three (3) times in order to
derive a solution to a problem.  Is this the best or most efficient
and effective way to implement this?  The table (which consists of
only five (5) FLOAT8 columns) I'm querying contains less than 50 rows.Thanks in advance for any insight or criticisms
youoffer.
 

CREATE OR REPLACE FUNCTION poly_example() RETURNS SETOF FLOAT8 AS
$poly_example$
DECLARE   term blah%ROWTYPE;   sigma_l FLOAT8 := 0.0;   sigma_b FLOAT8 := 0.0;   sigma_r FLOAT8 := 0.0;

BEGIN   FOR term in SELECT * FROM blah LOOP       sigma_l := sigma_l + (RANDOM() * 100) * (term.i * term.i) +
   RANDOM() * (term.j * term.j) + term.k;   END LOOP;
 
   FOR term in SELECT * FROM blah LOOP       sigma_b := sigma_b + (RANDOM() * 53) * (term.i * term.i) +
(RANDOM()* 5) * (term.j * term.j) + term.k;   END LOOP;
 
   FOR term in SELECT * FROM blah LOOP       sigma_r := sigma_r + 96.232234 * (term.i * term.i) +
0.32322325* (term.j * term.j) + term.k;   END LOOP;
 
   RETURN NEXT sigma_l + sigma_b + sigma_r;
END;
$poly_example$ LANGUAGE plpgsql STRICT IMMUTABLE;

-- Gary Chambers

/* Nothing fancy and nothing Microsoft! */


В списке pgsql-sql по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: How to order varchar data by word
Следующее
От: Rob Sargent
Дата:
Сообщение: Re: Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function