时间戳

首页 > 解决方案 > SQL select 语句产生与 PL/pgSQL FOR 循环相同的结果

sql - SQL select 语句产生与 PL/pgSQL FOR 循环相同的结果

问题描述

我很难理解何时使用 PL/pgSQLFOR循环与常规 SQL。

这个问题肯定显示了我的天真，但这是我感到困惑的地方：

给定这张表：

CREATE TABLE bins  AS 
SELECT * FROM GENERATE_SERIES(1, 10) AS id;

如果我想id在每一行的字段中添加一个加 1，我会这样做：

select id+1 from bins;

这将正确返回

2
3
4
5
6
7
8
9
10
11

现在，如果我为此操作使用 PL/pgSQL 函数，它看起来像这样：

CREATE OR REPLACE FUNCTION add_one(
n integer
) 
RETURNS VOID AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id
        FROM bins
    LOOP 
 RAISE NOTICE '%', rec.id+n;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

那么那么

select add_one(1)

会产生 

-- Executing query:
select add_one(1)
NOTICE:  2
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  3
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  4
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  5
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  6
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  7
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  8
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  9
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  10
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE
NOTICE:  11
CONTEXT:  PL/pgSQL function add_one(integer) line 9 at RAISE

所以我的问题是，SQL select 语句在某种程度上就像一个 for 循环，因为在这个例子中，它为每一行添加了一个 1 ......这与 PL/pgSQL 函数中发生的事情是一样的。这是一个准确的描述吗？

标签： sqlpostgresqlplpgsql

解决方案

Your description is accurate.

What is probably your biggest problem at this point is to understand the non-procedural nature of SQL. In this language you do not specify the steps required to reach a result, you only describe the result you want.

So the SQL statement you used above that you want id, increased by one, for every row in the table. If the database does this by running a loop internally or by an act of god is not your primary focus.

This is weird for many procedural programmers in the beginning, but it makes life better once you get used to it: you don't have to know all the database internals to figure out the best way to process a query. The optimizer does that for you, and usually better than you could do it yourself.

Of course it is healthy curiosity to want to know how a query is processed internally. For that you use the EXPLAIN statement. This is particularly necessary to analyze and fix performance problems.

The rule of thumb is that it is usually better to perform as much of the work as possible in one bigger SQL statement than in many simple small ones tied together with application code. This is because there is less overhead this way, but also because a bigger statement often opens more ways to the optimizer. For example, nested loops are the simplest, but not always the best way to solve a problem.

推荐阅读