Golang从Postgresql函数获取值，该函数返回一个int但也接收一个字符串数组

Question

I have a postgresql function that basically returns a number, but also as you can see the function receive an array of string.

Create Or Replace Function fnRegisterUserRoleArray(idUserFather int, rolesArray Text[]) returns int language plpgsql
as
$body$
declare ids INT;
declare roleID INT;
declare sanitazedRole TEXT;
declare counter int = 0;
begin
if(empty2null(idUserFather::text) is null) then
    ids := 0;
elsif exists( select 1 from win_users where id_user = idUserFather limit 1) then
    for counter in 1 .. array_upper(rolesArray, 1)
    loop
        select id_role from win_roles where rolename = rolesArray[counter] into roleID;
        insert into win_user_role(id_user, id_role) values (idUserFather, roleID);
        ids := ids + 1;
    end loop;
else
    ids := 0;
end if;
return ids;
end $body$;

and in my Go Function I have my variable database that receive the connection with the postgresql database,

database, err := getConnection()

however when I call the function fnRegisterUserViewsPermission and send the values I receive an error.

This is how I set the values:

var resultRole int 
err = database.QueryRow("Select fnRegisterUserRoleArray($1, $2);", resultUser, pq.Array(roleArray)).Scan(&resultRole, &int)
    fmt.Println(resultRole)
    if err != nil {
        fmt.Println(resultRole)
        return nil, err
    }
    if resultRole != 0 && resultRole != 1 {
        response = response + "their roles has been assigned correctly "
    } else {
            response = response + "however there was an error during the assignation of the role."
    }

and the output that I receive is this:

"message": "sql: Scan error on column index 0: converting driver.Value type (\"\") to a int: invalid syntax",

But the values get stored in my database, so the function receives in a good way the values, but the return is where it goes bam :(

This only occurs when i send an Array but if i send anything rather than an array the Scanner return the Id obtained from the postgresql function.

Is there any way to obtain the result id but also send the array in a more elegant way?

this is the values that contains role array:

[Carrier Brand]

And the value that contains resultUser is just a number, that represent the id of the user that has been registered in the database, in this case the registered user has an ID of...

result user: 63

Thanks! :)

Answer 1

As the error says:

"message": "sql: Scan error on column index 0: converting driver.Value type (\"\") to a int: invalid syntax",

The error is when scanning the result coming from the function when the value is returned from it.

Remove the pointer to int primitive type, Since only single value is returned form the function which is of int type.

Scan the value into int type variable as

var resultRole int 
err = database.QueryRow("Select fnRegisterUserRoleArray($1, $2);", resultUser, pq.Array(roleArray)).Scan(&resultRole)

For handling these types of situations it will be better if you handle scanning the result separately from the query as:

sqlStatement := `Select fnRegisterUserRoleArray($1, $2);`
var resultRole int 
// Replace 3 with an ID from your database or another random
// value to test the no rows use case.
row := db.QueryRow(sqlStatement, resultUser, pq.Array(roleArray))
switch err := row.Scan(&resultRole); err {
case sql.ErrNoRows:
  fmt.Println("No rows were returned!")
case nil:
  fmt.Println(resultRole)
default:
  panic(err)
}

Using above approach of separating queryRow with Scan help you to analyze more about the result and returned errors.