英文:
Go SQLX sqlx.DB Get("sql_function") does not return any value
问题
这是我的函数：
func (a *AirlinesRepository) Get(id int) *models.Airline {
airline := models.Airline{}
a.DB.Get(&airline, "SELECT * FROM fn_airlines_getby_id(?)", id)
return &airline
}
在执行DB.Get之后，airline的所有属性都被设置为零值，而不是用SQL函数的结果填充。我还尝试过使用"SELECT fn_airlines_getby_id(?)"和"fn_airlines_getby_id(?)"
Airline的属性如下：
type Airline struct {
Id int64 `db:"id"`
Name string `db:"name"`
}
函数如下：
CREATE OR REPLACE FUNCTION fn_airlines_getby_id(p_id INT)
RETURNS TABLE (id INT, name VARCHAR) AS
$$
BEGIN
RETURN QUERY
SELECT a.id AS id, a.name AS name FROM airlines as a WHERE a.id = p_id;
END
$$ LANGUAGE plpgsql;
更新：
查看Postgres的日志，我发现以下内容：
ERROR: syntax error at or near ")"
STATEMENT: SELECT fn_airlines_getby_id(?)
所以似乎sqlx没有解析应该替代?的值（它在普通查询中工作正常）
这是一个Postgres数据库，该函数是存在的。
英文:
This is my function:
func (a *AirlinesRepository) Get(id int) *models.Airline {
airline := models.Airline{}
a.DB.Get(&airline, "SELECT * FROM fn_airlines_getby_id(?)", id)
return &airline
}
After executing DB.Get, airline has all the properties set with zero value instead of being populated with the sql function result. I Also tried with "SELECT fn_airlines_getby_id(?)" and "fn_airlines_getby_id(?)"
Airline has the properties:
type Airline struct {
Id int64 `db:"id"`
Name string `db:"name"`
}
And the function is as follows:
CREATE OR REPLACE FUNCTION fn_airlines_getby_id(p_id INT)
RETURNS TABLE (id INT, name VARCHAR) AS
$$
BEGIN
RETURN QUERY
SELECT a.id AS id, a.name AS name FROM airlines as a WHERE a.id = p_id;
END
$$ LANGUAGE plpgsql;
UPDATE:
Looking at the Postgres's logs I found the next:
ERROR: syntax error at or near ")" at character 30
STATEMENT: SELECT fn_airlines_getby_id(?)
So seems that sqlx is not parsing the value that should go instead of ? (It works well with normal queries)
This is a Postgres database and the function exists.
答案1
得分: 0
使用Postgres的参数占位符代替sqlx的占位符。将以下代码进行更改：
从：
SELECT fn_airlines_getby_id(?)
改为：
SELECT * FROM fn_airlines_getby_id($1)
英文:
Use Postgres's parameter placeholder instead of sqlx one.
Change from
SELECT fn_airlines_getby_id(?)
To:
SELECT * FROM fn_airlines_getby_id($1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论