Scribd
Upload
4 views2 pages

PostgreSQL Assignment

The document explains PostgreSQL triggers and functions, highlighting how triggers automatically invoke functions during specific events like INSERT or UPDATE to maintain data integrity. It details various types of functions, including aggregate functions that perform calculations on sets of values, window functions that calculate across specific rows, and user-defined functions for reusable logic. The document emphasizes the importance of these tools in optimizing database operations and simplifying complex queries.

Uploaded by

vogis54184
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views2 pages

PostgreSQL Assignment

The document explains PostgreSQL triggers and functions, highlighting how triggers automatically invoke functions during specific events like INSERT or UPDATE to maintain data integrity. It details various types of functions, including aggregate functions that perform calculations on sets of values, window functions that calculate across specific rows, and user-defined functions for reusable logic. The document emphasizes the importance of these tools in optimizing database operations and simplifying complex queries.

Uploaded by

vogis54184
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

INTRODUCTION

DEFINITIONS

TRIGGERS: A PostgreSQL trigger is a powerful tool that allows automatic


invocation of a function whenever a specified event occurs on a table.
Events that can trigger a function include INSERT, UPDATE, DELETE,
or TRUNCATE. Triggers help maintain data integrity and automate
complex database operations.

A trigger is a special user-defined function associated with a table. To


create a new trigger, you must define a trigger function first, and then
bind this trigger function to a table. The difference between a trigger and a
user-defined function is that a trigger is automatically invoked when an
event occurs.

FUNCTIONS: A PostgreSQL function is a pre-written, reusable code block


to perform a specific task. It is created and stored in the database and can
be called from an SQL statement or another function.
They are mainly used to perform calculations, manipulate data, and
retrieve information from the database. They can take one or more
input parameters and return a single value.

TYPES OF FUNCTIONS

Aggregate Functions

An aggregate function is a function that performs a calculation on a set of


values and returns a single value.

Aggregate functions are often used with the GROUP BY clause of


the SELECT statement. The GROUP BY clause splits the result-set into
groups of values and the aggregate function can be used to return a single
value for each group.

The most commonly used SQL aggregate functions are:

 MIN() - returns the smallest value within the selected column

 MAX() - returns the largest value within the selected column

 COUNT() - returns the number of rows in a set

 SUM() - returns the total sum of a numerical column

 AVG() - returns the average value of a numerical column

NB: Aggregate functions ignore null values (except for COUNT()).


Window Functions

A window function in SQL is a type of function that allows us to


perform calculations across a specific set of rows related to the current
row. These calculations happen within a defined window of data, and
they are particularly useful for aggregates, rankings, and cumulative
totals without altering the dataset.

The OVER clause is key to defining this window. It partitions the data into
different sets (using the PARTITION BY clause) and orders them (using
the ORDER BY clause). These windows enable functions
like SUM(), AVG(), ROW_NUMBER(), RANK(), and DENSE_RANK() to be
applied in a sophisticated manner

User-Defined Functions

PostgreSQL allows developers to create user-defined


functions to encapsulate reusable logic, making database operations
more efficient and modular. The CREATE FUNCTION statement is used
to define a new function, supporting various procedural languages,
with plpgsql being the most commonly used in PostgreSQL.

The CREATE FUNCTION statement in PostgreSQL is a powerful tool for


defining custom functions that can be reused throughout
our database operations. These functions can
accept parameters, perform operations, and return values. Functions
are especially useful for simplifying complex queries and centralizing
logic that can be executed multiple times without rewriting the code.

TRIGGER FUNCTIONS

You might also like