Feed on
Posts
Comments

Overloading in PostgreSQL

>

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.

One Response to “Overloading in PostgreSQL”

  1. What i do not understood is in reality how you’re now not actually a lot more smartly-favored than you might be right now. You are very intelligent. You recognize thus significantly on the subject of this subject, made me personally imagine it from a lot of various angles. Its like men and women are not interested except it’s something to accomplish with Woman gaga! Your own stuffs great. All the time maintain it up!

Leave a Reply