database/sql: support output parameters

[kardianos]

It may be possible to add support output parameters. Look into the benefit and cost of doing so.

[kardianos]

The Oracle driver has support for output parameters but can't expose it in the sql package at this time:
https://github.com/rana/ora

[gopherbot]

CL https://golang.org/cl/38533 mentions this issue.

[kardianos]

@mattn @jackc @tgulacsi @mjibson @cbandy @julienschmidt @methane

Feedback requested for https://golang.org/cl/38533 . I need assistance in determining the correct API for OUTPUT parameters with regards to INOUT parameters.

Currently I specify an argument is for an OUTPUT parameter by passing a pointer value inside of

// Out may be used to retrieve OUTPUT value parameters from stored procedures.
//
// Drivers that support Out must implement the driver.NamedValueChecker.
//
// Example usage:
//
//   var outArg string
//   _, err := db.ExecContext(ctx, "ProcName", sql.Named("Arg1", Out{Dest: &outArg}))
type Out struct {
	// Dest is a pointer to the value to populate with the result of the
	// stored procedure's OUTPUT parameter.
	Dest interface{}
}

This works because this is part of a larger CL that allows drivers to accept additional parameter types.

Right now for MS SQL Server POC I'm sending the value to the server for both OUTPUT and INOUT situations and setting the fByRefValue flag on it. This works for MS SQL Server.

Do we need to differentiate between OUTPUT and INOUT parameters for Oracle, Posgresql, or MySQL? For instance, should we have an In bool field or something similar? On some drivers, maybe we ignore that field, but others use it to determine if it gets set to the server or not?

[jackc]

I don't think it will matter for PostgreSQL. It does not have true stored procedures, it has functions. And the idiomatic way of calling them is with select. Function output params are just a convenient way for returning a row instead of a single value.

So for PG I think it would remain idiomatic to use QueryRow("select myfunc(42)").Scan(...)

[decibel]

Technically, for Postgres it would be better to do "SELECT * FROM myfunc(...)". I know there's some changes around calling an SRF in the SELECT clause coming in 10, though I don't recall the details.