Learning PostgreSql: Functions and refcursors

In this post we shall create a function that returns data, and invoke it from our C# client. There are no stored procedures in PostgreSql, only functions. This is different from T-SQL, but consistent with many other languages, such as C#.

Creating a function

Functions can return many different types. Learning all the available options might take some time.

However, for the project we are working on, we need to replicate several T-SQL stored procedures which take column list as a parameter, and use dynamic SQL to return requested columns. So, we need the ability to return a result set from PostgreSql without having to specify exactly its structure. Because refcursors fit the bill, we shall learn how to use them, and that should be enough for now.

The following function returns a result set, although it does not use dynamic SQL yet:

CREATE OR REPLACE FUNCTION public.SelectTest (pTicker VARCHAR)RETURNS refcursor AS$body$DECLARE
  ref1 refcursor;BEGIN

OPEN ref1 FOR
select * from Prices WHERE Ticker=pTicker;RETURN ref1;
END;$body$
LANGUAGE 'plpgsql'

This function explicitly returns a result set. It is not enough to just issue a SELECT command. Again, this is different from T-SQL, but consistent with many object-oriented languages.

Calling the function from C# 

Let us invoke our function from C#:

[Test, Explicit]public void ReadFromProc2(){
   using (var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
   {
       conn.Open();
       using (var tran = conn.BeginTransaction())
       using (var command = conn.CreateCommand())
       {
           command.CommandText = "SelectTest";
           command.CommandType = CommandType.StoredProcedure;
           command.Parameters.Add(new NpgsqlParameter());
           command.Parameters[0].NpgsqlDbType = NpgsqlDbType.Varchar;
           command.Parameters[0].Value = "AAPL";
           using (var dr = command.ExecuteReader())
           {
               var rc = 0;
               while (dr.Read())
               {
                   Console.WriteLine((rc++) + " " + dr.GetString(0) + " " + dr.GetDateTime(1) + " " + dr.GetDecimal(2));
               }
           }
       }
   }
}

Note: this code snippet does not handle null values. This is done to keep the example short.

Note:  it is essential to keep the transaction open for all the time we are consuming the data.This is documented here:http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html

Using dynamic SQL

We need to make sure we can call from C# functions that use dynamic SQL - that is part of the functionality we are going to replicate. The following function uses a dynamic column list:

CREATE OR REPLACE FUNCTION public.SelectTestWithColumnList (Ticker VARCHAR, ColumnList VARCHAR)RETURNS refcursor AS$body$DECLARE
  ref1 refcursor;BEGIN

OPEN ref1 FOR EXECUTE 'select ' || ColumnList || ' from Prices WHERE Ticker=$1;'USING     Ticker;RETURN ref1;
END;$body$
LANGUAGE 'plpgsql' 

Of course, this is all completely different from T-SQL.

The following code calls this function from C#:

 [Test, Explicit]public void ReadFromProcWithColumnList(){
   using (var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
   {
       conn.Open();
       using (var tran = conn.BeginTransaction())
       using (var command = conn.CreateCommand())
       {
           command.CommandText = "SelectTestWithColumnList";
           command.CommandType = CommandType.StoredProcedure;
           command.Parameters.Add(new NpgsqlParameter());
           command.Parameters[0].NpgsqlDbType = NpgsqlDbType.Varchar;
           command.Parameters[0].Value = "AAPL";
           command.Parameters.Add(new NpgsqlParameter());
           command.Parameters[1].NpgsqlDbType = NpgsqlDbType.Varchar;
           command.Parameters[1].Value = "AsOfDate,Price";
           using (var dr = command.ExecuteReader())
           {
               var rc = 0;
               while (dr.Read())
               {
                   Console.WriteLine((rc++) + " " + dr.GetDateTime(0) + " " + dr.GetDecimal(1));
               }
           }
       }
   }
}

This code works as expected.

Next steps

There is much more to be learned about functions. We shall get back to it later.

However, we want to move over the data to our new database. We need some meaningful data to practice with. While the data is being migrated, we shall continue our learning.