英文:
How do I listen to a Postgres stored procedure trigger from Go?
问题
在Postgres数据库中，我有一个可以通过以下方式触发的存储过程：
SELECT add_customer('name', 'lastname')
我希望从我的Go代码中监听这个触发器。
我尝试过以下方法：
import (
"github.com/jmoiron/sqlx"
"github.com/lib/pq"
)
// 之前的代码..
db, _ := sqlx.Connect("postgres", connStr)
// 假设db已连接并正常工作
minReconn := 10 * time.Second
maxReconn := time.Minute
listener := pq.NewListener(psqlInfo, minReconn, maxReconn, reportProblem)
err = listener.Listen("add_customer")
if err != nil {
return nil, err
}
在我的代码的main函数中，我调用一个等待通知的函数：
go func() {
somepackage.WaitForNotification()
}()
函数的代码如下：
func (k *someStruct) WaitForNotification() {
select {
// 这个case永远不会满足
case notification := <-k.db.Listener.Notify:
k.logger.Info(fmt.Sprintf("%v", notification))
err := doWork()
// ...
}
}
如注释所述，上述代码路径永远不会被执行。
根据文档搜索结果，存储过程与Postgres中的LISTEN/NOTIFY事件不同。
我该如何在Go中监听存储过程的触发？
英文:
In a Postgres database, I have a stored procedure that can be triggered as such:
SELECT add_customer('name', 'lastname')
I wish to listen to this trigger from my Go-code.
What I've tried:
import (
"github.com/jmoiron/sqlx"
"github.com/lib/pq"
)
// Code beforehand..
db, _ := sqlx.Connect("postgres", connStr)
// Assume db is connected and works
minReconn := 10 * time.Second
maxReconn := time.Minute
listener := pq.NewListener(psqlInfo, minReconn, maxReconn, reportProblem)
err = listener.Listen("add_customer")
if err != nil {
return nil, err
}
In main of my code, I call a function that waits for notifications:
go func() {
somepackage.WaitForNotification()
}()
That looks like:
func (k *someStruct) WaitForNotification() {
select {
// This case is never satisfied
case notification := <-k.db.Listener.Notify:
k.logger.Info(fmt.Sprintf("%v", notification))
err := doWork()
// ...
}
}
As commented, the above code path is never reached.
Searching docs, it is evident that the stored procs are not the same as LISTEN/NOTIFY events in Postgres.
How do I listen to a stored procedure being triggered from Go?
答案1
得分: 1
更新你的add_customer函数，以便向频道发送通知。要发送通知，请使用NOTIFY命令或pg_notify(text, text)函数。
CREATE OR REPLACE FUNCTION add_customer(first_name text, last_name text) RETURNS void
LANGUAGE plpgsql STRICT
AS $_$
BEGIN
NOTIFY add_customer, '<payload>';
-- 或者
SELECT pg_notify('add_customer', '<payload>');
-- 其余的代码
END
$_$;
英文:
Update your add_customer function to send a notification to the channel. To send the notification use the NOTIFY command or the pg_notify(text, text) function.
CREATE OR REPLACE FUNCTION add_customer(first_name text, last_name text) RETURNS void
LANGUAGE plpgsql STRICT
AS $_$
BEGIN
NOTIFY add_customer, '<payload>';
-- or
SELECT pg_notify('add_customer', '<payload>');
-- the rest of your code
END
$_$;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论