postgresql - 如何将参数传递给用 PL/pgSQL 编写的查询？

Question

我想知道是否可以将参数传递给用 PL/pgSQL 编写的查询？

我试过了，但是失败了 pq: got 1 parameters but the statement requires 0

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

func main() {
    db, err := sql.Open("postgres", "host=localhost dbname=db user=user sslmode=disable password=pw")
    if err != nil {
        log.Fatal(err)
    }
    row := db.QueryRow(`
DO $$
BEGIN
    IF true THEN
        SELECT $1;
    END IF;
END$$
`, 1)
    var num int
    err = row.Scan(&num)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(num)
}

另一个相关的问题是我想使用事务，但是sql包提供的API似乎每次在tx中执行查询时都会连接到db。如果可能的话，我希望一次性执行所有操作。例如，在 go 中你应该使用这样的事务

tx, err := db.Begin()
rows, err := tx.Query(sql1)
result, err := tx.Exec(sql2)
tx.Commit()

问题是，如果我没记错的话，调用 tx.Query 和 tx.Exec 会两次访问 PostgreSQL 服务器。我想要实现的是合并 sql1 和 sql2，将它们包装在 BEGIN 和 END 中并在其中执行一次旅行。我的问题是:

1. 您认为有必要吗？我想如果有足够的流量，性能差异可能会很明显，但我不确定。
2. 如果是这样，执行此合并交易的最佳方式是什么？创建一个函数并在 PL/pgSQL 中运行事务(因为我可能需要使用条件语句等)？

Answer 1

你得到错误是因为 PL/pgSQL 应该在服务器端定义为 function 或 procedure，但在你的情况下，它是从客户端调用的。下面是一个关于如何定义和调用带有参数的函数的简单示例:

CREATE OR REPLACE FUNCTION myadd(a integer, b integer) RETURNS integer AS $$
    BEGIN
            RETURN a + b;
    END;
$$ LANGUAGE plpgsql;

然后，您可以从客户端使用 SELECT 查询调用带参数的函数。请注意，即使您的函数包含 INSERT/UPDATE，也必须使用 SELECT 语句调用该函数。

//...
a := 10
row := db.QueryRow(`SELECT * FROM myadd($1, $2)`, a, 130)
//...

下一个问题，关于事务和 PL/pgSQL。是的，使用 PL/pgSQL 可以减少网络流量。 Several advantages服务器端语言 (PL/pgSQL) 是:

1. 消除客户端-服务器往返
2. 无需将中间结果传输给客户端，只会传输最终结果。
3. 避免多次解析查询(向服务器发送查询 --> 服务器解析查询 --> 执行数据库操作 --> 返回结果给客户端等...)

处理数据库(大数据)的规则是你需要避免移动你的数据，PL/pgSQL 符合这个规则。然而，有些情况下你不能(需要避免)使用 PL/pgSQL，例如数据库管理员/服务器所有者不允许服务器端编程(安全/性能原因等)。

function 和transaction 之间的关系在the manual 中有明确说明。 :

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction

总而言之，使用 PL/pgSQL 您可能会获得性能提升。多少？是依赖。请记住，使用 PL/pgSQL 后，您需要管理多个代码库，有时很难调试。