PostgreSQL - Constants

In PostgreSQL, constants are immutable values used in SQL queries. These constants can be of various types, such as strings, numbers, and special values like NULL. Here's a look at the different types of constants in PostgreSQL:

String Constants:
Strings are sequences of characters, and in SQL, they are typically surrounded by single quotes ('). For example:
```
SELECT 'Hello, World!';
```
If you need to include a single quote inside a string, you can do so by typing two single quotes in succession, like ''This is a string''.
Integer Constants:
Integers are whole numbers without any decimal point. They can be positive, negative, or zero. For example:
```
SELECT 12345;
```
Floating-Point Constants:
Floating-point numbers are numbers with a decimal point. They can be represented in standard or exponential notation. For example:
```
SELECT 123.45;
SELECT 1.23e4;  -- This is equivalent to 12300
```
Bit-String Constants:
Bit-strings are binary values, and they are often preceded by a B or X. For example:
```
SELECT B'11001';
SELECT X'4D';  -- Represents the byte value of 4D in hexadecimal (M in ASCII)
```
Boolean Constants:
PostgreSQL supports the boolean data type with values TRUE, FALSE, and NULL. For example:
```
SELECT TRUE;
```
Date and Time Constants:
Date and time values are typically represented as strings, but PostgreSQL interprets them based on context. For example:
```
SELECT '2021-09-01'::date;
SELECT '15:30:00'::time;
```
The :: is a type-cast operator, which explicitly converts a constant to a particular data type.
Special Constants:
NULL is a special constant in SQL that represents an unknown or missing value.
```
SELECT NULL;
```
UUID Constants:
PostgreSQL supports the UUID data type, which represents Universally Unique Identifiers. They can be used as constants in a similar fashion:
```
SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;
```
Array Constants:
PostgreSQL supports array data types, and you can create constant arrays in queries:
```
SELECT '{1,2,3,4}'::int[];
```

Constants can be used in various parts of SQL queries, from the SELECT clause to the WHERE clause, and combined with various operators and functions to form more complex expressions.

Defining and using constants in PostgreSQL:

Description: Constants are fixed values that do not change during the execution of a query or transaction.

Code:

-- Defining a constant
DO $$ 
DECLARE 
    my_constant CONSTANT INTEGER := 42; 
BEGIN 
    -- Using the constant
    RAISE NOTICE 'The constant value is: %', my_constant; 
END $$;

Global vs. local constants in PostgreSQL:

Description: Constants can be defined globally for a session or locally within a specific scope.

Code (Global):

-- Define a global constant
SET my_global_constant = 10;

Code (Local):

-- Define a local constant in a PL/pgSQL block
DO $$ 
DECLARE 
    my_local_constant CONSTANT INTEGER := 20; 
BEGIN 
    -- Using the local constant
    RAISE NOTICE 'The constant value is: %', my_local_constant; 
END $$;

Constants in PL/pgSQL functions in PostgreSQL:

Description: PL/pgSQL functions can have constants defined within their scope.

Code:

CREATE OR REPLACE FUNCTION my_function()
RETURNS VOID AS $$
DECLARE
    my_constant CONSTANT INTEGER := 30;
BEGIN
    -- Using the constant within the function
    RAISE NOTICE 'The constant value is: %', my_constant;
END;
$$ LANGUAGE plpgsql;

Handling constant values in PostgreSQL queries:
- Description: Constants can be used in regular SQL queries to represent fixed values.
- Code:
```
SELECT * FROM my_table WHERE column1 = 40;
```

Constants in PostgreSQL stored procedures:

Description: Constants can be used in stored procedures, providing a way to define and use fixed values.

Code:

CREATE OR REPLACE PROCEDURE my_procedure()
AS $$
DECLARE
    my_constant CONSTANT INTEGER := 50;
BEGIN
    -- Using the constant within the procedure
    RAISE NOTICE 'The constant value is: %', my_constant;
END;
$$ LANGUAGE plpgsql;

Immutable vs. mutable constants in PostgreSQL:

Description: Immutable constants are values that do not change over time. Mutable constants can be altered.

Code (Immutable):

CREATE FUNCTION my_immutable_function() RETURNS INTEGER AS $$
DECLARE
    my_immutable_constant CONSTANT INTEGER := 60;
BEGIN
    RETURN my_immutable_constant;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Code (Mutable):

CREATE FUNCTION my_mutable_function() RETURNS INTEGER AS $$
DECLARE
    my_mutable_constant CONSTANT INTEGER := 70;
BEGIN
    my_mutable_constant := my_mutable_constant + 1;
    RETURN my_mutable_constant;
END;
$$ LANGUAGE plpgsql;

Using constants for indexing in PostgreSQL:
- Description: Constants can be used as fixed values for indexing or as parameters in query conditions.
- Code:
```
CREATE INDEX idx_my_table_column1 ON my_table(column1, my_constant);
```

Constants in PostgreSQL views and triggers:

Description: Constants can be used in view definitions or trigger functions to represent fixed values.

Code (View):

CREATE VIEW my_view AS
SELECT * FROM my_table WHERE column1 = my_constant;

Code (Trigger):

CREATE OR REPLACE FUNCTION my_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    NEW.column1 := NEW.column1 + my_constant;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;