THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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.

Published Friday, November 01, 2013 4:25 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement