postgres tutorial 10 procedure declare variable, constant, raise message

 postgreSQL evaluates the default values and set them for the variables when declared

DO $$ 

DECLARE

   counter    INTEGER := 1;

   first_name CONSTANT  VARCHAR(50) := 'John';

   last_name CONSTANT   VARCHAR(50) := 'Doe';

   payment    NUMERIC(11,2) := 20.5;

   created_at time := NOW();

BEGIN 

   RAISE NOTICE '% % % has been paid % USD @ %', counter, first_name, last_name, payment, created_at;

   PERFORM pg_sleep(1);

   counter := counter + 1;

   payment := 25;

   RAISE NOTICE '% correction % % has been paid % USD @ %', counter,  first_name, last_name, payment, created_at;

END $$;

 -------------------------------

NOTICE:  1 John Doe has been paid 20.50 USD @ 21:39:00.540055

NOTICE:  2 correction John Doe has been paid 25.00 USD @ 21:39:00.540055

DO

Query returned successfully in 1 secs 24 msec.

------------------------------------

define a variable named city_name with the same data type as name the name column of the city table

city_name city.name%TYPE := 'San Francisco';

define an alias for any variable as follows:

new_name ALIAS FOR old_name;

----------------------------------
RAISE statement

DO $$
BEGIN
  RAISE INFO 'information message %', now() ;
  RAISE LOG 'log message %', now();
  RAISE DEBUG 'debug message %', now();
  RAISE WARNING 'warning message %', now();
  RAISE NOTICE 'notice message %', now();
END $$;

DO $$
DECLARE
 email varchar(255) := 'info@postgresqltutorial.com';
BEGIN
  -- check email for duplicate
  -- ...
  -- report duplicate email
  RAISE EXCEPTION 'Duplicate email: %', email
 USING HINT = 'Check the email again';
END $$;

If the condition evaluates to TRUE, ASSERT statement does nothing. If the condition evaluates to FALSE or NULL, the ASSERT_FAILURE is raised.

DO $$
DECLARE
 counter integer := -1;
BEGIN
   ASSERT counter = 0
   MESSAGE 'Expect counter starts with 0';
END $$;