Wednesday, March 12, 2014

Learning PostgreSql: overloading

Functions in PL/PgSql can be overloaded, which is very different from T-SQL.

Demonstrating overloading 

For example, the second CREATE FUNCTION in the following script does not replace the first function - it creates a second one:
CREATE OR REPLACE FUNCTION public.GetQuoteOfTheDay(someNumber INTEGER)RETURNS VARCHAR AS$body$BEGIN

RETURN 
'Say my name.';
END;$body$
LANGUAGE plpgsql
;

CREATE OR REPLACE FUNCTION public.GetQuoteOfTheDay(someNumber REAL)RETURNS VARCHAR AS$body$BEGIN

RETURN 
'I am the danger.';
END;$body$
LANGUAGE plpgsql
;
Let us verify that two functions have been created:
SELECT FROM public.GetQuoteOfTheDay(1)UNION ALL SELECT FROM public.GetQuoteOfTheDay(1.2);
The output shows both "Say my name." and "I am the danger.".
This means that function name alone is not enough to uniquely identify a function. As a result, we cannot drop a function unless we provide its signature. The following command fails:
DROP FUNCTION public.GetQuoteOfTheDay;
Once we have provided the function's signature, we can drop it:
DROP FUNCTION public.GetQuoteOfTheDay(INTEGER);

Optional parameters

Optional parameters in PL/PgSql also behave differently from T-SQL. In T-SQL we cannot add an optional parameter to a function without breaking all the code that uses it. Consider, for example, the following T-SQL function:
CREATE FUNCTION dbo.TestOptionalParameters @i INT@optional INT = )RETURNS TABLE
AS RETURN
  
SELECT  @i AS ,
            
@optional AS Optional
  
) ;GO
On SQL 2008 R2, we cannot invoke it with only one parameter; we must provide both:
-- this worksSELECT FROM dbo.TestOptionalParameters(12);
-- this does not compileSELECT FROM dbo.TestOptionalParameters(1);
Msg 313Level 16State 3Line 2
An insufficient number 
of arguments were supplied for the procedure or function dbo.TestOptionalParameters.
This behavior is both counter-intuitive for C# developers and not consistent with T-SQL stored procedures. The expected way to invoke a T-SQL function with an optional parameter is as follows:
SELECT FROM dbo.TestOptionalParameters(1DEFAULT);
As a result, if we add an optional parameter to a function, we still need to change all the code that invokes it. This makes T-SQL optional parameters less useful than they could be, less useful than optional parameters in other languages.
The following script demonstrates the use of optional parameters in PL/PgSql:
 CREATE OR REPLACE FUNCTION public.EchoName(FirstName VARCHAR = 'Jesse'LastName VARCHAR = 'Pinkman')RETURNS VARCHAR AS$body$DECLAREret VARCHAR;BEGIN

RETURN 
LastName || ', ' || FirstName;
END;$body$
LANGUAGE plpgsql
;
SELECT FROM public.EchoName()UNION ALL SELECT FROM public.EchoName('Walter')UNION ALL SELECT FROM public.EchoName('Walter''White');
This script outputs the following results: "Pinkman, Jesse", "Pinkman, Walter", "White, Walter".

Parameter's optionality does not change function's signature

For example, let us recreate the function which we dropped earlier: 
CREATE OR REPLACE FUNCTION public.GetQuoteOfTheDay(someNumber INTEGER)RETURNS VARCHAR AS$body$BEGIN

RETURN 
'Say my name.';
END;$body$
LANGUAGE plpgsql
;
The following script will replace it:
CREATE OR REPLACE FUNCTION public.GetQuoteOfTheDay(someNumberINTEGER 0)RETURNS VARCHAR AS$body$BEGIN

RETURN 
'No more half-measures.';
END;$body$
LANGUAGE plpgsql
;
When we select from the function, we shall get "No more half measures" in both cases:
SELECT FROM public.GetQuoteOfTheDay(1)UNION ALL SELECT FROM public.GetQuoteOfTheDay(); 

Parameter names are optional

Theoretically, we do not have to provide parameters' names. The following script shows a function which does exactly the same thing, although it is less readable:

DROP FUNCTION public.EchoNameVARCHAR,  VARCHAR);CREATE OR REPLACE FUNCTION public.EchoNameVARCHAR = 'Jesse',  VARCHAR = 'Pinkman')RETURNS VARCHAR AS$body$
DECLAREret VARCHAR;BEGIN

RETURN 
$1 || ', ' || $2;
END;$body$
LANGUAGE plpgsql;
   
We can rerun the following script, and get exactly the same results as before:
SELECT FROM public.EchoName()UNION ALL SELECT FROM public.EchoName('Walter')UNION ALL SELECT FROM public.EchoName('Walter''White');

Npgsql driver disregards parameter names too

The following code returns results even though both parameters of the function being invoked do not have names:
[Test, Explicit]
public void ReadFromProcWithOptionalParams(){
   using (var conn new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
   
{
       conn.Open();
       
for (var i 03i++)
           
using (var tran = conn.BeginTransaction())
           
using (var command conn.CreateCommand())
           
{
               command.CommandText "EchoName"
               command.CommandType CommandType.StoredProcedure;
               
if(i>0)
               
{
                   var firstName new NpgsqlParameter("
LastName"
                                        NpgsqlTypes.NpgsqlDbType.Varchar);
                   
command.Parameters.Add(firstName);
                   
command.Parameters[0].Value "Hank"
               }
               if (== 2)
               
{
                   var lastName new NpgsqlParameter("
FirstName"
                                        NpgsqlTypes.NpgsqlDbType.Varchar);
                   
command.Parameters.Add(lastName);
                   
command.Parameters[1].Value "Schrader"
               }

               using (var dr command.ExecuteReader())
               
{
                   while (dr.Read())
                   
{
                       Console.WriteLine(dr.GetString(0));
                   
}
               }
           }
   }
}
  
If the function's parameters do have names, NpgSql does not match parameter names at all. Instead, it submits the first value to the first parameter, and so on. For example, let us restore the version of our function with named parameters:
DROP FUNCTION public.EchoNameVARCHAR,  VARCHAR);CREATE OR REPLACE FUNCTION public.EchoName(FirstName VARCHAR = 'Jesse'LastName VARCHAR = 'Pinkman')RETURNS VARCHAR AS$body$DECLAREret VARCHAR;BEGIN

RETURN 
LastName || ', ' || FirstName;
END;$body$
LANGUAGE plpgsql
;
Now let us rerun ReadFromProcWithOptionalParams().
Even though we are passing "Hank" to a parameter named "LastName", "Hank" is passed to the first parameter, as we can see from the output:

Jesse, Pinkman
Hank, Pinkman
Hank, Schrader

This is a breaking change.We shall have to change our client code in many places.
Alternatively, are considering if we want to change NpgSql, so that it uses parameter names instead of ignoring them - that would make migration of our client code so very much easier. Because NpgSql is open source, changing it should not be a problem. We do not have to plead our case and, in the best case scenario, wait months or years for the change. If needed, we can get it done it soon.
In the next post we shall briefly discuss PostgreSql's MVCC vs SQL Server's snapshot isolation. This is a huge topic; we do not have the time for a more detailed comparison at this time.

No comments:

Post a Comment