如何在Go中监听Postgres存储过程触发器?

huangapple go评论82阅读模式
英文:

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(&#39;name&#39;, &#39;lastname&#39;)

I wish to listen to this trigger from my Go-code.

What I've tried:

    import (
        &quot;github.com/jmoiron/sqlx&quot;
	    &quot;github.com/lib/pq&quot; 
    )

    // Code beforehand..

	db, _ := sqlx.Connect(&quot;postgres&quot;, connStr)
    // Assume db is connected and works

	minReconn := 10 * time.Second
	maxReconn := time.Minute
	listener := pq.NewListener(psqlInfo, minReconn, maxReconn, reportProblem)
	err = listener.Listen(&quot;add_customer&quot;)
	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 := &lt;-k.db.Listener.Notify:
		k.logger.Info(fmt.Sprintf(&quot;%v&quot;, 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, &#39;&lt;payload&gt;&#39;;
    -- or
	SELECT pg_notify(&#39;add_customer&#39;, &#39;&lt;payload&gt;&#39;);
	
    -- the rest of your code
END
$_$;

huangapple
  • 本文由 发表于 2021年5月26日 14:47:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/67699848.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定