如何从函数（存储过程）获取INPUT值？与高朗

问题描述：

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

答

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;

如何从函数（存储过程）获取INPUT值？ 与高朗

相关推荐

如何从函数（存储过程）获取INPUT值？与高朗