如何使用npgsql从PG/SQL函数中获取返回值

Question

在Microsoft.Net中使用npgsql模块，如何获得PostgreSQL函数的返回值？不是命名参数或ref游标的值，而是返回的单个字符串/数值？

谢谢！

Martin

Answer 1

因此，我有一个PL/pgSQL函数声明如下（我使用的是EnterpriseDB[版本9.5]，我不确定这在普通PostgreSQL中是否有效）：

FUNCTION f_auth_user (p_user_name           IN     VARCHAR2,
                     p_user_pwd            IN     VARCHAR2,
                     p_security_user_seq      OUT NUMBER,
                     p_login_key              OUT VARCHAR2,
                     p_psn_id                 OUT NUMBER,
                     p_message                OUT VARCHAR2)
  RETURN NUMBER;

调用它的dotNet代码看起来是这样的：

            EDBCommand cmd = new EDBCommand("smcs.pkg_login.f_auth_user(:p_user_name, :p_user_pwd, :p_security_user_seq, :p_login_key, :p_psn_id, :p_message)", conn); 
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("p_return_value", EDBTypes.EDBDbType.Integer, 256).Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add("p_user_name", EDBTypes.EDBDbType.Varchar2, 256).Value = txtUserName.Text.Trim().ToUpper();
            cmd.Parameters.Add("p_user_pwd", EDBTypes.EDBDbType.Varchar2, 256).Value = txtPassword.Text.Trim().ToUpper();
            cmd.Parameters.Add("p_security_user_seq", EDBTypes.EDBDbType.Integer, 256).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("p_login_key", EDBTypes.EDBDbType.Varchar2, 256).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("p_psn_id", EDBTypes.EDBDbType.Integer, 256).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("p_message", EDBTypes.EDBDbType.Varchar2, 256).Direction = ParameterDirection.Output;

这种通用方法适用于Oracle，但似乎我需要将添加返回值参数的代码移动到参数列表的末尾，所以我的工作代码如下：

            cmd.Parameters.Add("p_user_name", EDBTypes.EDBDbType.Varchar2, 256).Value = txtUserName.Text.Trim().ToUpper();
            cmd.Parameters.Add("p_user_pwd", EDBTypes.EDBDbType.Varchar2, 256).Value = txtPassword.Text.Trim().ToUpper();
            cmd.Parameters.Add("p_security_user_seq", EDBTypes.EDBDbType.Integer, 256).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("p_login_key", EDBTypes.EDBDbType.Varchar2, 256).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("p_psn_id", EDBTypes.EDBDbType.Integer, 256).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("p_message", EDBTypes.EDBDbType.Varchar2, 256).Direction = ParameterDirection.Output;
            returnParam = new EDBParameter("p_return_value", EDBTypes.EDBDbType.Integer);
            returnParam.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnParam);

我很可能已经将p_return_value参数的创建和使用压缩为2或1个语句，但我还没有尝试过。

然后我可以执行并检索结果（包括返回值），如下所示：

            conn.Open();
            cmd.Prepare();
            cmd.ExecuteNonQuery();
            sequence = Convert.ToInt32(cmd.Parameters["p_security_user_seq"].Value.ToString());
            loginKey = Convert.ToString(cmd.Parameters["p_login_key"].Value.ToString());
            message = Convert.ToString(cmd.Parameters["p_message"].Value.ToString());
            authenticatedUser = Convert.ToString(cmd.Parameters["p_return_value"].Value.ToString());
            conn.Close();

谢谢！

Martin