英文:
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.SecondmaxReconn := time.Minutelistener := 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 worksminReconn := 10 * time.SecondmaxReconn := time.Minutelistener := 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 satisfiedcase 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 voidLANGUAGE plpgsql STRICTAS $_$BEGINNOTIFY 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 voidLANGUAGE plpgsql STRICTAS $_$BEGINNOTIFY add_customer, '<payload>';-- orSELECT pg_notify('add_customer', '<payload>');-- the rest of your codeEND$_$;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论