Npgsql fetching records from Stored Procedures

Question

I have tried to execute the codes below to search for a username

using (var cmd = new NpgsqlCommand("search_users", conn))
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@username", NpgsqlTypes.NpgsqlDbType.Text, "xxx");

    using (var reader = cmd.ExecuteReader())
        while (reader.Read())
            x.Add(reader["loginname"].ToString());
}

On the line of cmd.ExecuteReader() I'm getting this error

Npgsql.PostgresException: '42601: a column definition list is required for functions returning "record"'

Am I doing the right way to read the returned records ? This is the stored procedure

CREATE FUNCTION search_users(username TEXT) RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
      SELECT * from public."Users" as t1
      WHERE t1."LoginName" = username INTO ret;
RETURN ret;
END;$$ LANGUAGE plpgsql;

Any help is greatly appreciated.

Possible duplicate of https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret — Fourat, Feb 02 '18 at 08:57
Possible duplicate of [PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"](https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret) — Fourat, Feb 02 '18 at 08:57

score 0 · Answer 1 · answered Feb 06 '18 at 23:04

If you want to return a record that matches the layout of public."Users" then I think all you need to do is specify that in the return type:

CREATE FUNCTION search_users(username TEXT) RETURNS public."Users" AS $$
DECLARE 
  ret RECORD;
BEGIN
      SELECT * from public."Users" as t1
      WHERE t1."LoginName" = username INTO ret;
RETURN ret;
END;$$ LANGUAGE plpgsql;

As far as the C# code... I see what you are trying to do, and I think if this were Oracle that approach would work perfectly. In PostgreSQL, the line between a function and a stored procedure is a little blurred (in my opinion), and the easiest way to execute the function and return the results is with a simple select statement. As such, your C# would look as follows:

using (var cmd = new NpgsqlCommand("select * from search_users(:USER_NAME)", conn))
{
    cmd.Parameters.AddWithValue("USER_NAME", "xxx");

    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
            x.Add(reader["loginname"].ToString());
    }
}

The use of the @ versus the : totally doesn't matter... I only switched to the colon for personal style preferences.

Npgsql fetching records from Stored Procedures

1 Answers1