Unlike variables, the value of constants cannot be changed once initialized. The main purpose of the use of constants in PostgreSQL are:
- It makes the query more readable.
- It reduces the maintenance efforts.
Syntax: constant_name CONSTANT data_type := expression;
Let’s analyze the above syntax:
- First, specify the constant name. By convention, it is generally in the uppercase form.
- Second, put the CONSTANT keyword and specify the data type that the constant is associated with.
- Third, initialize a value for the constant.
Example 1:
The following example declares a constant named VAT for valued added tax and calculates the selling price from the net price:
DO $$
DECLARE
VAT CONSTANT NUMERIC := 0.1;
net_price NUMERIC := 20.5;
BEGIN
RAISE NOTICE 'The selling price is %', net_price * ( 1 + VAT );
END $$;
Output:
Now let’s attempt to change the constant as below:
DO $$
DECLARE
VAT constant NUMERIC := 0.1;
net_price NUMERIC := 20.5;
BEGIN
RAISE NOTICE 'The selling price is %', net_price * ( 1 + VAT );
VAT := 0.05;
END $$;
As expected it raises an error as shown below:
Example 2:
It is important to note that PostgreSQL evaluates the value for the constant when the block is entered at run-time, not compile-time as shown in the below example:
DO $$
DECLARE
start_at CONSTANT time := now();
BEGIN
RAISE NOTICE 'Start executing block at %', start_at;
END $$;
Output:
PostgreSQL evaluates the NOW( ) function every time we call the block. To prove it, we execute the block again:
And got a different result.
Last Updated :
28 Aug, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...