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: 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 = 0 )
RETURNS TABLE
AS RETURN
  
( SELECT  @i AS i ,
            
@optional AS Optional
  
) ;
GO

On SQL 2008 R2, we cannot invoke it with only one parameter; we must provide both:

-- this works
SELECT * FROM dbo.TestOptionalParameters(1, 2);

-- this does not compile
SELECT * FROM dbo.TestOptionalParameters(1);

Msg 313, Level 16, State 3, Line 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(1, DEFAULT);

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$
DECLARE
ret 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(someNumber INTEGER = 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.EchoName( VARCHAR,  VARCHAR);
CREATE OR REPLACE FUNCTION public.EchoName( VARCHAR = 'Jesse',  VARCHAR = 'Pinkman')
RETURNS VARCHAR AS
$body$
DECLARE
ret 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 = 0; i < 3; i++)
           
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 (i == 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.EchoName( VARCHAR,  VARCHAR);
CREATE OR REPLACE FUNCTION public.EchoName(FirstName VARCHAR = 'Jesse', LastName VARCHAR = 'Pinkman')
RETURNS VARCHAR AS
$body$
DECLARE
ret 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.

 

 

 

Published Tuesday, November 05, 2013 4:17 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

 

Pavel Stehule said:

A main difference between T-SQL and PostgreSQL is fact, so PostgreSQL support overloading based on parameter' types - list of types defines function signature. When you use a overloaded function, PostgreSQL choose a signature with the least number of conversions (most similar signature to used parameters). When PostgreSQL cannot to choose exactly (there are more similar functions with same distance, then PostgreSQL raises error.

A described feature is overloading - not polymorphism (in PostgreSQL terminology). Polymorphism is using a polymorphic types like "anyelement, anyarray, ..", and it is similar to C++ templates.

November 6, 2013 2:16 PM
 

Alexander Kuznetsov said:

Pavel,

I've just corrected the title and such. Thank you!

November 6, 2013 2:27 PM
 

Ian Yates said:

In my old Delphi code using the ADO COM objects to talk to SQL Server I was bitten by parameter names being ignored very early on.  The ADO library (or perhaps Delphi's wrappers) didn't really care how the parameters were named.

From that point onwards I always stuck new parameters at the end of existing stored proc parameter lists for this very reason.

In more modern times I use something like Entity Framework and let it worry about generating nice statically typed C# methods for my procs & functions.  Should I update my database I just have EF regenerate the model from the DB and then go fix my C# code.  I have compilers - why not let them catch the obvious bugs in my code for me? :)

November 6, 2013 11:08 PM
 

Alexander Kuznetsov said:

Ian,

I am with you - of course nobody manually writes boilerplate code like the C# examples in this post. Of course it is going to be generated one way or another.

We can generate it ourselves easily, or we can go for an ORM. Either way this is not going to be a problem.

I am just very curious about the technology I am working with. I love experimenting and playing with it.

November 7, 2013 3:25 PM

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 at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

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