EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Functions

PostgreSQL Functions

By Priya PedamkarPriya Pedamkar

PostgreSQL Functions

Introduction to PostgreSQL Functions

PostgreSQL functions are also called as a stored procedure in PostgreSQL; You set a PostgreSQL stored procedure or function within SQL statements. Create a statement used to create a new function; we can create a PostgreSQL function in many languages like C, Python, SQL, and PL/pgsql. Before PostgreSQL version 11, PostgreSQL relied heavily on functions, but after that, the database differentiated between functions and procedures. Its function name will be unique, which means it will not be the same as other names of a function that was already created in the database.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Below given is the syntax:

CREATE [OR REPLACE (Replace the function if already exist)] FUNCTION
name (Name of function) ( [ [ argmode (Mode of argument)] [ argname(Name of argument) ] argtype (Type of argument)[ { DEFAULT | = } default_expr (Default expression of function) ] [, ...] ] )
[RETURNS rettype (Return type of functions)
| RETURNS TABLE (Table name which we have used to return from function)(column_name (Name of column) column_type (Data type of column)[, ...] ) ]
{LANGUAGE lang_name (Language name which was used to create function)
| WINDOW (Window function)
| IMMUTABLE | STABLE | VOLATILE (Attribute of function)
| COST execution_cost (Execution costs)
| ROWS result_rows (No of rows returned from function)
| SET configuration_parameter (Specified configuration parameter) {TO value | = value | FROM CURRENT}
| AS 'definition' (Definition of function)
} ...
[WITH (attribute [, ...] ) ] (Display optional function information)

Below is the parameter description of the above syntax:

  • Create: Creates a new function.
  • Function name: Name of function which we are creating. The function name should be unique.
  • Replace: Replace the function name which is already present in the database.
  • Argmode: Mode of argument which we have used in a function, the default argument mode of function is IN.
  • Argname: Name of argument which we have used to create a function.
  • Argtype: Type of argument which we have used to create a function.
  • Default: The default behavior is to use a default value if no argument is given.
  • Rettype: This defines as a return type of functions. The return type can be anything composite, base, or table column.
  • Table: Table name which column we have returned from a function.
  • Column name: This defines that the name of columns that we have a return in return table syntax.
  • Column type: This defines as a data type of column that we have returned in the return table syntax.
  • Language name: We define the language used to create a function by specifying its name. The language can be Python, pl/pgsql, or C.
  • Window: This defines as creating a window function instead of a plain one.
  • Execution cost: This parameter defines the total time required to execute the function; it shows the positive number.
  • Cost: The cost parameter define the execution cost of the function.
  • Rows: Rows parameter defines as the total rows returned from the function.
  • Result rows: Result of rows that have returned from the function.
  • Configuration parameter: This defines a configuration parameter set as a different value.
  • Definition: This parameter defines a definition of a function.
  • Attribute: This will display extra information about a function.

How do PostgreSQL Functions work?

  • In PostgreSQL, people also refer to functions as stored procedures or stored functions.
  • A PostgreSQL stored procedure or function consists of a set of SQL statements or PL/SQL statements.
  • Its name will be unique means it will not be the same as other names of a function that have already been created in the database.
  • Create a statement is used in a function to create a new function; also, we can replace the existing function using replace keyword at the time of new function creation.
  • In PostgreSQL, we can create a new function in various languages such as C, Python, SQL, and PL/pgSQL. To define a new function in PostgreSQL, we use the CREATE statement.
  • Prior to version 11, a function in PostgreSQL was required to behave like a PostgreSQL procedure. However, starting from version 11, functions and procedures are distinguished from each other.
  • In PostgreSQL, a stored procedure or function refers to a set of SQL statements stored on the database server and invoked through a SQL interface.
  • In PostgreSQL, we use a function to execute multiple SQL queries and return the results in the format specified in the function definition.
  • We can check all functions from the database by using a “\df”. This command is used to display all functions from a database.
  • We can implement it in different languages. It will choose the default language if we do not define the language during function creation.
  • It uses an interface that defines an argument and return function type, as stated in the function’s syntax.

Examples of PostgreSQL Functions

Given below is the example to create, modify and drop a function:

Example #1: Create a PostgreSQL function

Below is an example of create a new function.

Code:

CREATE OR REPLACE FUNCTION Func_Test ()
RETURNS integer AS $test$
declare
test integer;
BEGIN
SELECT count (1) into test FROM EMP;
RETURN test;
END;
$test$ LANGUAGE plpgsql;
\df

Output:

PostgreSQL Function 1

In the above example, we have created a function name as Func_Test. We have used the pl/pgsql language to create a new function.

A count record from the EMP table is available. The commands that we can use to call or run Func_Test are as follows.

Code:

select Func_Test();
\df+

Output:

PostgreSQL Function 2JPG

Example #2: Modify the PostgreSQL function

We are changing the owner of the Func_Test function from postgres to test user.

Code:

ALTER FUNCTION Func_Test OWNER to test;
\df+

Output:

modify

Example #3: Drop the PostgreSQL function

We can drop by using a drop command. We are dropping the Func_Test function.

Code:

drop function Func_Test;
\df+

Output:

drop

Conclusion

A PostgreSQL function or stored procedure is a set of SQL statements that are stored on the database server and invoked through a SQL interface. The create statement is used to create a new function in PostgreSQL. We can implement this function in any languages like C, Python, and pl/pgsql.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL UNIQUE Constraint
  2. PostgreSQL LAG()
  3. Top 9 Parameters of PostgreSQL log
  4. Guide to PostgreSQL While Loop
PROGRAMMING LANGUAGES Course
502+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Certification Course
57+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System - Design & Develop an IOT System
65+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Certification Course
19+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Certification Course
 17+ Hour of HD Videos
4 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more