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

Question

I wonder if it's possible to pass parameters to a query written in PL/pgSQL?

I tried this, but it failed with 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)
}

Another related question is that I want to use transactions, but the APIs provided by the sql package seems to connect to db every time a query is executed in a tx. I'd like everything to be executed in one go if that's possible. For example, with go you are supposed to use transactions like this

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

The problem is that calling tx.Query and tx.Exec makes two trips to PostgreSQL server if I'm not wrong. What I want to achieve is to merge sql1 and sql2, wrap them inside BEGIN and END and execute them in one trip. And my question is that:

1. Do you think it's necessary? I imagine that with enough traffic, the performance difference might be obvious, but I'm not sure.
2. If so, what's the best way to execute this merged transaction? Create a function and run the transaction inside PL/pgSQL (since I might need to use conditional statements, etc)?

Answer 1

You got error because PL/pgSQL is supposed to be defined in server side as function or procedure, but in your case, its being called from client side. Below is a simple example on how to define and call the function with parameter(s):

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

Then, from client side you can call the function with parameters using SELECT query. Please note, even though your function contains INSERT/UPDATE, the function must be called using SELECT statement.

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

Next question, about transaction and PL/pgSQL. Yes, using PL/pgSQL you can reduce network traffic. Several advantages of server side language (PL/pgSQL) are:

1. Eliminate client-server round trip
2. No need to transfer intermediate result to client, only the final result will be transferred.
3. Avoid parsing queries multiple times (Send query to server --> server parsing query --> perform database operation --> return result to client, etc...)

The rule when dealing with database (large data) is You need to avoid to move your data around and PL/pgSQL fits this rule. However, there are some circumstances in which you can not (need to avoid) use PL/pgSQL, e.g. DB admin/server owner does not allow server side programming (security/performance reason etc).

Relation between function and transaction is clearly stated in 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

In summary, using PL/pgSQL you may get performance improvement. How much? It's depend. Please keep in mind, after using PL/pgSQL you need to manage more than one codebase, and sometimes it's difficult to debug.