PL/pgSQL
Declaring Variables
DO
$$
DECLARE
mynum integer := 89;
first_name varchar(20) := 'Uday';
hire_date date := '2020-01-01';
start_time timestamp := NOW();
emptyvar integer;
BEGIN
RAISE NOTICE 'My variable % % % % %',
mynum, first_name, hire_date,
start_time, emptyvar ;
END;
$$Parameters to Function
CREATE OR REPLACE FUNCTION function_name
(INT, INT) RETURNS INT as
$$
DECLARE
x alias for $1;
y alias for $2;
BEGIN
--
END;
$$Assigning value from result into variable
DO
$$
DECLARE
product_title products.product_name%TYPE;
BEGIN
SELECT product_name FROM products
INTO product_title
where product_id = 1 limit 1;
RAISE NOTICE 'Your product name is %', product_title;
END;
$$ Function Parameter w/t IN and OUT
CREATE OR REPLACE FUNCTION fn_sum_using_inout
( IN x integer, IN y integer, OUT Z integer ) as
$$
BEGIN
z := x+y;
END;
$$
LANGUAGE PLPGSQL;
select fn_sum_using_inout(2,3);
-- another example
CREATE OR REPLACE FUNCTION fn_sum_using_inouts
( IN x integer, IN y integer, OUT Z integer, OUT w integer ) as
$$
BEGIN
z := x+y;
w := x*y;
END;
$$
LANGUAGE PLPGSQL;
select * from fn_sum_using_inouts(2,3);Nested functions
DO
$$
<< Parent >>
DECLARE
counter integer := 0;
BEGIN
counter := counter+1;
RAISE NOTICE 'the current value of counter (IN PARENT) is %', counter;
DECLARE
counter integer := 0;
BEGIN
counter := counter + 5;
RAISE NOTICE 'The current value of counter at subblocks is %', counter;
RAISE NOTICE 'The parent value of counter at subblocks is %', PARENT.counter;
END;
counter := counter + 5;
RAISE NOTICE 'the current value of counter (IN PARENT) is %', counter;
END;
$$
LANGUAGE PLPGSQL;Returning ResultSet from function
CREATE OR REPLACE FUNCTION fn_order_by_date_pro() RETURNS SETOF orders AS
$$
BEGIN
RETURN QUERY SELECT * FROM orders limit 10;
END;
$$
LANGUAGE PLPGSQL;
SELECT * FROM fn_order_by_date_pro();Conditional Statement inside functions
Default Parameters
CREATE OR REPLACE FUNCTION fn_which_is_greater
( x integer default 0, y integer default 0 ) RETURNS text AS
$$
BEGIN
IF x > y then
return ' x > y ';
else
return ' x < y ';
end if ;
END;
$$ LANGUAGE PLPGSQL;
SELECT fn_which_is_greater(4,3);Switch Case Example
CREATE OR REPLACE FUNCTION fn_checker
( x integer default 0 ) RETURNS text AS
$$
BEGIN
CASE x
when 10 then
return 'value = 10';
when 20 then
return 'value = 20';
else
RETURN 'MORE';
END CASE;
END;
$$ LANGUAGE PLPGSQL;
SELECT fn_checker(30);Loops in PLPGSQL
DO
$$
DECLARE
i_counter integer = 0;
BEGIN
LOOP
RAISE NOTICE '%', i_counter;
i_counter := i_counter+1;
EXIT WHEN
i_counter = 5;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;Loops in range exaple
DO
$$
BEGIN
FOR counter IN 1..5 BY 1
LOOP
RAISE NOTICE 'COUNTER : %', counter;
END LOOP;
END;
$$
LANGUAGE PLPGSQL;Reverse Loops
DO
$$
BEGIN
FOR counter IN REVERSE 5..1 BY 1
LOOP
RAISE NOTICE 'COUNTER : %', counter;
END LOOP;
END;
$$
LANGUAGE PLPGSQL;Iterating over result set
DO
$$
DECLARE
rec record ;
BEGIN
FOR rec in
select order_id, customer_id from orders LIMIT 10
LOOP
RAISE NOTICE '% %', rec.order_id, rec.customer_id;
END LOOP;
END;
$$ LANGUAGE PLPGSQLLoop with Exit condition
DO
$$
DECLARE
i_counter int = 0;
BEGIN
LOOP
i_counter = i_counter + 1;
EXIT WHEN
i_counter > 20;
CONTINUE
WHEN MOD (i_counter,2) = 0;
RAISE NOTICE 'COUNTER : %', i_counter;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;Declaring arrays in PLPGSQL
DO
$$
DECLARE
arr1 int[] := array[1,2,3];
arr2 int[] := array[4,5,6,7,8];
var int;
BEGIN
FOREACH var IN ARRAY arr1||ARR2
LOOP
RAISE NOTICE '%', var;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;While Loop in PLPGSQL
CREATE OR REPLACE FUNCTION fn_while_loop_sum_all(x integer)
returns numeric as
$$
DECLARE
counter integer := 1;
sum_all integer := 0;
BEGIN
WHILE counter <= x
LOOP
sum_all := sum_all + counter;
counter := counter + 1;
END LOOP;
return sum_all;
END;
$$ language plpgsql
select fn_while_loop_sum_all(4);Returning specific Query from column
CREATE OR REPLACE FUNCTION fn_api_products_by_names(p_pattern varchar)
RETURNS TABLE ( productname varchar, unitprice real )
AS
$$
BEGIN
RETURN QUERY
SELECT product_name,unit_price from products
where product_name like p_pattern;
END;
$$ LANGUAGE PLPGSQL;
SELECT * FROM fn_api_products_by_names('A%');CREATE OR REPLACE FUNCTION fn_all_orders_greater() RETURNS SETOF order_details as
$$
DECLARE
r record;
BEGIN
for r in
select * from order_details where unit_price > 100
loop
return next r;
end loop;
return;
end;
$$ language plpgsql;
select * from fn_all_orders_greater();If data not found condition
DO
$$
DECLARE
rec record;
orderid smallint = 1;
BEGIN
SELECT customer_id, order_date
FROM orders
INTO STRICT rec
WHERE order_id = orderid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'No order id was found';
END;
$$ LANGUAGE PLPGSQL; Throwing execption on condition
DO
$$
DECLARE
rec record;
orderid smallint = 1;
BEGIN
SELECT customer_id, order_date
FROM orders
INTO STRICT rec
WHERE order_id > 1000;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'Too many rows were found';
END;
$$ LANGUAGE PLPGSQL; Throwing execption example
CREATE OR REPLACE FUNCTION fn_div_exception (x real, y real) RETURNS real as
$$
DECLARE
ret real;
BEGIN
ret := x / y;
return ret;
EXCEPTION
WHEN division_by_zero then
RAISE INFO 'division by zero error';
RAISE INFO 'ERROR % %', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE PLPGSQL;
SELECT fn_div_exception(5,0);Last updated