User Defined Type

Sequence

Introduction

• A DB sequence, or database sequence, is a database object used to generate unique sequential values for a column in a table.

• In other words, a sequence is a database object that generates a sequence of unique integer values, which can be used as the primary key or a unique identifier for a column in a table. A sequence is often used to generate IDs for new records in a table, ensuring that each new record has a unique identifier.

• When a sequence is created in a database, it is given an initial value, which is the starting point for the sequence. Each time a value is generated from the sequence using the NEXTVAL function, the sequence is incremented by one, and the new value is returned. The sequence will continue to increase by one each time a new value is generated until it reaches its maximum value.

• On some relational db (e.g Postgres) , it is using sequence as auto-increment id

CREATE SEQUENCE IF NOT EXISTS "MessageTemplate_id_seq";

-- Table Definition
CREATE TABLE "public"."MessageTemplate" (
    "id" int4 NOT NULL DEFAULT nextval('"MessageTemplate_id_seq"'::regclass),
    "title" varchar(50) NOT NULL,
    PRIMARY KEY ("id")
);

Syntax

Create Sequence

CREATE SEQUENCE sequence_1
start with 1
increment by 1
minvalue 0
maxvalue 100
cycle;

Apply sequence into column

INSERT into students VALUES
(sequence_1.nextval,'Shubham');
INSERT into students VALUES
(sequence_1.nextval,'Aman');

Restart the sequence value

ALTER SEQUENCE '"LlmEngine_id_seq"' RESTART WITH 1;

Vs Auto-increment

Sequence > auto-increment

1. More control over the values generated: With a sequence, you have more control over the values generated for the primary key. For example, you can specify the starting value, the increment value, and the maximum value of the sequence, which can be useful in certain cases.

2. Avoiding gaps in the sequence: In some cases, auto-incrementing primary keys can result in gaps in the sequence due to rollbacks or deletes. With a sequence, you can avoid gaps by reserving a range of values for a transaction and rolling back the transaction if it's not used, ensuring that the sequence is always contiguous.

3. Cross-database compatibility: Sequences are supported by many relational database management systems, including Oracle, PostgreSQL, and SQL Server, which can make it easier to maintain cross-database compatibility when using different database systems.

4. Reducing contention: In some scenarios, auto-incrementing primary keys can cause contention and performance issues when multiple transactions are inserting data into the same table. Using a sequence can help reduce contention and improve performance by ensuring that each transaction can generate its own unique ID without waiting for other transactions to complete.

Auto-increment > Sequence

1. Simplicity: Auto-incrementing primary keys are very simple to use and require minimal configuration. If your database schema is relatively simple and you don't require the additional flexibility provided by a sequence, an auto-incrementing primary key may be more appropriate.

2. Small databases: If you are working with a small database with relatively few records, the benefits of using a sequence may not outweigh the additional complexity it adds to the database schema.

3. Guaranteed uniqueness: If you are confident that the rate of insertions into your database will not exceed the maximum value of the data type used for the auto-incrementing primary key, you can be assured that the primary key will always be unique, eliminating the need to use a sequence.

4. Database portability: Some databases may not support sequences, or may implement them in different ways. If you need to maintain database portability across different database systems, using an auto-incrementing primary key may be more appropriate.

Procedure

• It is a set of statement(s) that perform some defined actions. We make stored procedures so that we can reuse statements that are used frequently

// Create 
CREATE PROCEDURE us_customers AS
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';

// Execute
EXEC us_customers;

User Defined Function

• A function would return the returning value/control to the code or calling function. The procedures perform certain tasks in a particular order on the basis of the given inputs. A procedure, on the other hand, would return the control, but would not return any value to the calling function or the code.

// Syntax
CREATE OR REPLACE FUNCTION function_name (parameters)
RETURNS data_type AS
BEGIN
    SQL statements
    RETURN value
END;

// Create
CREATE FUNCTION getUsernames() RETURNS text  AS 
'SELECT "name"  FROM "User";' LANGUAGE SQL;

// Execute
SELECT getUsers();

// List the created function
SELECT routine_name AS function_name,
routine_type AS function_type,
data_type
FROM information_schema.routines
WHERE routine_type = 'FUNCTION' AND routine_schema = 'public';

Dynamic SQL

• Dynamic SQL refers to the creation and execution of SQL statements at runtime.

• Instead of writing a static SQL query where the structure and parameters are known at compile time, dynamic SQL allows you to construct SQL statements dynamically based on certain conditions or variables during the execution of a program or stored procedure.

BEGIN;
// execute the function
DO $$
DECLARE playgroundValue INTEGER;
        botId  INTEGER;
        playgroundResourceId INTEGER;
        playgroundPlanId INTEGER;
        featureFlagRecord RECORD;
// Logic begin
// Define the boundaries of a block of code
BEGIN

FOR featureFlagRecord IN 
SELECT "targetValue", fo."metaData" as "metaData" FROM "FeatureFlagOverride" fo JOIN "FeatureFlag" f ON fo."featureFlagId" = f.id WHERE f."key" = 'BOT.CONFIG_BOT_CHAT_CALL_RATE_LIMIT' AND fo."targetType" = 'BOT' 
LOOP

botId:= featureFlagRecord."targetValue";
playgroundValue:= (featureFlagRecord."metaData"->'PLAYGROUND' ->> 'points')::integer;

SELECT id INTO playgroundResourceId from "Resource" WHERE "resourceKey"
 = 'rate-limit-playground';

INSERT INTO "Plan" 
("resourceId", "planKey", "planName", "description", "entityId", 
"entityType", "groupEnvs")
VALUES
(playgroundResourceId, 'rate-limit-playground-plan-'|| botId, 
playgroundValue||' chats per hour', 
'To allow maximum '|| playgroundValue  ||' chats per hour by using Playground'
,botId, 'BOT', '{PROD,TEST}') 
RETURNING id INTO playgroundPlanId;

// logging
RAISE NOTICE 'data patch of rate limit, botId : %, playground: %, teams: %, apiKey: %', botId,playgroundValue, teamsValue, apiKeyValue;

END LOOP;
// end of boundary and function
end; $$;
COMMIT;

Trigger

• It is automatically called whenever a data modification event against a table takes place

// Syntax 
create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row]  
[trigger_body] 

// Create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
// [before | after]: This specifies when the trigger will be executed.
// {insert | update | delete}: This specifies the DML operation.
// On [table_name]: This specifies the name of the table associated with the trigger.
// [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each affected row.
// [trigger_body]: This provides the operation to be performed as the trigger is fired


// Example 1
create trigger deep 
on emp
for
insert,update ,delete
as 
print 'you can not insert,update and delete this table i';

// Example 2
CREATE TRIGGER stud_marks BEFORE INSERT ON Student FOR EACH ROW 
SET NEW.total =  NEW.item1 + NEW.item2;

View

• It is a virtual table based on the result-set of an SQL statement

• When selecting the view, it is actually executing the sql statement behind the scene, but it can simplify the code to make it more developer-friendly

// Create
CREATE VIEW CustomersInBrazil AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';

// Usage
SELECT * FROM  CustomersInBrazil;

Materialized View

• Similar with view, but when creating view, it will firstly execute the sql statement and store the result into view, so that when selecting from view, it will get the result stored without executing sql statement again

• When updating the data is required, it is needed the refresh the view by command, so that the sql statement behind the view will be executed again

• It is also a kind of cache instead of executing sql statement again