title: Postgres Stored Procedure from C#
body: <p>I have a Postgres database with a stored procedure that returns JSON documents, based on the article here: <a href="http://www.sqlines.com/postgresql/npgsql_cs_result_sets" rel="nofollow noreferrer">http://www.sqlines.com/postgresql/npgsql_cs_result_sets</a></p>
  
  <p>The procedure is represented like this:</p>
  
  <pre><code>-- Procedure that returns a single result set (cursor)
  CREATE OR REPLACE FUNCTION get_data_test() RETURNS refcursor AS $$
  DECLARE
  ref refcursor;              -- Declare a cursor variable
  BEGIN
      OPEN ref FOR            -- Open a cursor
          SELECT row_to_json(r) AS data
          FROM 
          (
              SELECT *
              FROM data AS d
          ) r;
      RETURN ref;             -- Return the cursor to the caller
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>I am then running the following code from a .net console app:</p>
  
  <pre><code>// Making connection with Npgsql provider
  using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
  {
      conn.Open();
  
      var trans = conn.BeginTransaction();
      var cmd = new NpgsqlCommand("get_data_test", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Transaction = trans;
  
      using (var reader = cmd.ExecuteReader())
      {
          while (reader.Read())
              Trace.WriteLine(reader[0]);
      }
  }
  </code></pre>
  
  <p>The output is  a single instance wit the name of the refcursor, rather than the actual data:
  &lt; unnamed portal 1 ></p>
  
  <p>If I run the select query directly in the command text, the result set is returned as expected. I've also tried explicitly calling the proc via text using "SELECT get_data_test()" but this also fails with just the cursor name.</p>
  
  <p>I don't believe I am missing a step and this refcursor should be returned unbundled. What am I doing wrong?</p>