Feed on
Posts
Comments
«
»

Overloading in PostgreSQL

Feb 6th, 2011 by

>

In this blog, I will explain overloading, an object oriented feature of PostgreSQL which outperforms other open source database technologies and even some commercial database technologies, as well.

Overloading
In an OOP environment, a procedure or function is overloaded to provide more flexibility and ease in day to day development. Here, I do not enter into any detail or explanation on overloading. For more information on overloading, visit this article on Wikipedia.

So What’s Next
First of all, you would have to download the query. The query has two tables namely “Countries” and “States”.

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE "Countries" (
    "CountryCode" CHAR(2) NOT NULL PRIMARY KEY,
    "CountryName" VARCHAR(100) NOT NULL,
    "CountryId" INTEGER NOT NULL
);
CREATE TABLE "States" (
    "CountryCode" CHAR(2) NOT NULL REFERENCES "Countries"("CountryCode"),
    "StateCode" VARCHAR(4) NOT NULL,
    "StateName" VARCHAR(100),
    UNIQUE("CountryCode", "StateCode")
);

Create a Function

Selec All Code:
1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE FUNCTION "TotalStates"()
RETURNS INTEGER
AS
$BODY$
    SELECT 
        CAST(COUNT(*) AS INTEGER) 
    FROM
        "States"
$BODY$
LANGUAGE SQL;

The function returns the total number of states in “States” table.

Now Let’s Overload the Function

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE FUNCTION "TotalStates"("CountryCode" CHAR(2))
RETURNS INTEGER 
AS
$BODY$
    SELECT 
        CAST(COUNT(*) AS INTEGER) 
    FROM 
        "States"
    WHERE 
        "CountryCode"=$1
$BODY$
LANGUAGE SQL;

The overloaded function of the first function would return the count of total states of a country.

Let’s Try It!
Normal Function

Selec All Code:
1

SELECT "TotalStates"() AS "Total";

Result

Selec All Code:
1
2
3

Total
-----
3940

Overloaded Function

Selec All Code:
1

SELECT "TotalStates"('US') AS "StatesInUS";

Result

Selec All Code:
1
2
3

StatesInUS
----------
57

Please Note
I have modified the original “ISO-3166″ sample database from Christopher Kings-Lynne for this example which can be downloaded from this link.

Tags: ,

Posted in PostgreSQL Server

Leave a Reply