PostgreSQL

Postgres Drop Function If Exists

PostgreSQL is an open-source database management system and the source code is available under the license of PostgreSQL. PostgreSQL database like other databases holds data in the tabular form. And this data is modified or updated by applying several commands to them.

To manage data or use it under the circumstances of a given situation, we have some storage containers that get data and perform operations on it. These are known as the functions or the stored procedures.

PostgreSQL Function

PostgreSQL functions are divided into two main functions. These are:

Built-in Functions

These functions are present in the PostgreSQL database, at the time of configuration of the database management system and while connecting with the server. You can use them by only using their name in the command. Some common examples of built-in functions are MAX (), MIN (), and COUNT () etc.

User-defined functions

These are the functions that are defined by the user. These functions are not already present but only can be created at the time of code introduction according to the current scenario. You can use built-in functions inside them, but as a whole, these functions only work when they are created manually. Some examples are like get_result (), Apply_interest (), etc. In this tutorial, we will talk about these types of functions.

Drop Function

Drop function deals with removing the already created function in PostgreSQL. Just like tables, functions are also dropped or get truncated. So to understand the concept of DROP functions if they exist, we need to first understand the function creation and their working. After that, we will come towards dropping them off.

Postgres Function Creation

Now, we will see the syntax to make a function. Just like a table in PostgreSQL, we will use a CREATE command but the procedure is different.

Syntax

CREATE FUNCTION name_of_function (arguments)

RETURNS datatype

LANGUAGE psql

AS $variable_name$

DECLARE

declaration;

( variable declaration )

BEGIN

< function_body >

( logic )

RETURN { variable_name | value }

END;

$$

Parameters of the above syntax are described as:

  • Name_of_Function: This parameter is used to give a name to the user-defined function. This is written after the keywords Create Function.
  • Arguments: This feature contains the parameters of the function that will be used inside the function.
  • Return: This feature returns the value in a specific data type that is defined after the RETURN keyword.
  • Language: This defines the programming language that is used in the function.
  • Function_body: This part is the main part of the user-defined function as it contains the logic or the condition made by the user.

We have implemented the function creation command on the pgAdmin dashboard. We will also consider them on the shell later in the article. So, open pgAdmin, connect with the server and select the tools option and then select QUERY tool. A window will be opened, write the command in that portion.

Now, consider an example in which we have created a function to store and return teacher’s information including teacher id, name, and title. We have written the main logic of the function creation command below. The whole code is mentioned in the image attached.

create or replace function get_teacher_info()
begin
   for rec in select
teacher_id,
            title,
            (first_name || ' ' || last_name):: varchar
        from teacher
        inner join teacher_info using(teacher_id)
        inner join subject using (subject_id)
        order by title
    return;
end;
$$

The information in the teacher info function joins two tables that contain the data relevant to the subject and the teacher having subject_id and teacher_id as the parameter. Now, execute the code by clicking the execute button at the top. On execution, a message is displayed that the function is created successfully.

This example of function creation was without the use of any argument. So now, we will again create a function with the same name, but the difference is that the function will accept a parameter with it.

So, this is the difference that will discriminate the new function from the previous one. That’s why another function with the same name as the previous one is created. Otherwise, it cannot be possible to create two functions with a similar name and similar arguments.

Like tables and databases, we can also observe the names of functions in the left navigation bar by expanding the server and databases; you will reach the Schemas of the database. On expanding schema, you will see an option ‘public’; this will lead you towards the FUNCTION option. All the created functions are listed here.

DROP Function

To drop a user-defined function from the database, we use a DROP statement. This statement is just like the command used for dropping the table.

Syntax

Drop function [if exists] name_of_function (arguments)

[Cascade | restrict];

Now, we will explain these parameters and their usage.

  • Firstly, we specify the name of the function that we want to delete by applying the drop statement. This is written after the keyword ‘DROP FUNCTION.
  • Secondly, we use an ‘if exist’ option, this helps the PostgreSQL database to display a message of an error if the specified function is not present.
  • Third deals with the list of the arguments of the function. As we have seen that functions can be with or without parameters, so PostgreSQL wants to know the function that we want to remove by checking the arguments we have applied for.
  • The CASCADE and the RESTRICT options are optional according to the condition drop statement implementation.

We will use a drop statement on the same function we have created above without any argument.

>> Drop function get_teacher_info;

This statement will produce an error, as it is not clear to PostgreSQL which function to drop, as we have created two functions of the same name with the argument specified. The solution is to use an empty parenthesis with the function showing zero parameters.

>> Drop function get_teacher_info();

This will work. From two, now one function is left behind. Using the name will be sufficient to drop the function.

>> Drop function get_teacher_info;

Or the second option is to use the command with the parameter to directly specify the function.

DROP Function in psql Shell

Similar to the function used in pgAdmin, we have created a function here.

>> Create function get_house_Price(Price_from int, Price_to int)

returns int

language plpgsql

as

$$

Declare

house_count integer;

Begin

select count(*)

into house_count

from house

where house_price between Price_from and Price_to;

return house_count;

End;

$$;

Now, we will see the created function, whereas all other functions can be displayed by using a psql command. This command brings the list of functions along with the name schema data type and the arguments of the function.

>> \df

To drop the function, now we will use the drop command with the arguments.

>> DROP function get_house_price(price_from integer, price_to integer);

Conclusion

The article ‘Postgres drop function’ is implemented on PostgreSQL database management system on both pgAdmin dashboards and the psql as well by using Windows 10. Firstly, we have created a function to explain the working of a function. Then, the drop command is applied to the functions in both ways, with or without parameters.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.