英文:
How to get INOUT value from a function (stored procedure)? with GOLANG
问题
如何从函数（存储过程）中获取一个INOUT参数的值，比如我想获取v_id的值：
CREATE OR REPLACE FUNCTION mytest(
IN v_name character varying,
INOUT v_id bigint)
RETURNS bigint AS
$BODY$
Begin
v_id := 99;
select v_name;
return ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
当我调用这个函数时，看到这个，并且我使用来自github.com/lib/pq的库，我的程序出现了panic：
func main() {
dbstring := "user=postgres dbname=xxx password=xxx host=127.0.0.1 port=5432 sslmode=disable"
db, err := sql.Open("postgres", dbstring)
db.SetMaxIdleConns(4)
db.SetMaxOpenConns(10)
if err != nil {
panic(err)
}
var mynum int64 = 0
var s string
a, err2 := db.Query("SELECT mytest($1::TEXT,$2::bigint)","lihao",mynum)
if err2 != nil {
fmt.Fprintln(os.Stderr, "Error sending mytest:", err)
//os.Exit(1)
}else{
fmt.Fprintln(os.Stdout, "a is:",a)
fmt.Fprintln(os.Stdout, "mynum is:",mynum)
}
for a.Next() {
err = a.Scan(&s, &mynum)
if err != nil {
panic(err)
}
fmt.Fprintln(os.Stdout, "mynum is:",mynum)
}
}
出现了panic，所以在这里：
D:\Go>fs_con.exe
Error sending mytest:
panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x20 pc=0x48d4b9]
goroutine 1 [running]:
database/sql.(*Rows).Next(0x0, 0xc082028018)
c:/go/src/database/sql/sql.go:1633 +0x29
main.main()
D:/Go/fs_con.go:35 +0x48d
goroutine 5 [chan receive]:
database/sql.(*DB).connectionOpener(0xc0820683c0)
c:/go/src/database/sql/sql.go:634 +0x4c
created by database/sql.Open
c:/go/src/database/sql/sql.go:481 +0x33d
英文:
ALL
How to get a INOUT parameter value from a function(stored procedure) so as,I want to get the value of v_id :
CREATE OR REPLACE FUNCTION mytest(
IN v_name character varying,
INOUT v_id bigint)
RETURNS bigint AS
$BODY$
Begin
v_id := 99;
select v_name;
return ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
When I call this func, look this,and I use the library from github.com/lib/pq,and my program has panic:
func main() {
dbstring := "user=postgres dbname=xxx password=xxx host=127.0.0.1 port=5432 sslmode=disable"
db, err := sql.Open("postgres", dbstring)
db.SetMaxIdleConns(4)
db.SetMaxOpenConns(10)
if err != nil {
panic(err)
}
var mynum int64 = 0
var s string
a, err2 := db.Query("SELECT mytest($1::TEXT,$2::bigint)","lihao",mynum)
if err2 != nil {
fmt.Fprintln(os.Stderr, "Error sending mytest:", err)
//os.Exit(1)
}else{
fmt.Fprintln(os.Stdout, "a is:",a)
fmt.Fprintln(os.Stdout, "mynum is:",mynum)
}
for a.Next() {
err = a.Scan(&s, &mynum)
if err != nil {
panic(err)
}
fmt.Fprintln(os.Stdout, "mynum is:",mynum)
}
}
it is panic, so here:
D:\Go>fs_con.exe
Error sending mytest:
panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x20 pc=0x48d4b9]
goroutine 1 [running]:
database/sql.(*Rows).Next(0x0, 0xc082028018)
c:/go/src/database/sql/sql.go:1633 +0x29
main.main()
D:/Go/fs_con.go:35 +0x48d
goroutine 5 [chan receive]:
database/sql.(*DB).connectionOpener(0xc0820683c0)
c:/go/src/database/sql/sql.go:634 +0x4c
created by database/sql.Open
c:/go/src/database/sql/sql.go:481 +0x33d
答案1
得分: 0
你的函数不知道要返回什么结果。最好在函数中添加一个新的变量。
创建或替换函数 mytest(
IN v_name character varying,
IN v_id bigint)
返回 bigint AS
$BODY$
声明
result bigint;
开始
result := v_id + 1;
返回 result;
结束;
$BODY$
语言 plpgsql 不稳定
成本 100;
英文:
Your function doesn't know what result to return. And it's better to add new variable in function.
CREATE OR REPLACE FUNCTION mytest(
IN v_name character varying,
IN v_id bigint)
RETURNS bigint AS
$BODY$
DECLARE
result bigint;
BEGIN
result := v_id + 1;
return result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论