LOOP Syntax and examples of conditional Loop – End Loop. CREATE OR REPLACE FUNCTION get_loop(in p_id numeric) RETURNS varchar AS $$ DECLARE j numeric; v_name varchar(50); BEGIN j:=0; FOR i in 1..p_id LOOP j:=j+1; v_name:=’The count is: ‘||j; END LOOP; return v_name; END; $$ LANGUAGE ‘plpgsql’; Control structures | IF | IF Else | IF Elsif Else CASE

IF – Elsif – Else Syntax and examples of conditional IF – Elsif – Else. CREATE OR REPLACE FUNCTION get_result(in p_name varchar(2)) RETURNS varchar AS $$ DECLARE v_name varchar(50); BEGIN if p_name = ‘A’ then v_name:=’The name is A’; return v_name; elsif p_name = ‘B’ then v_name:=’The name is A’; return v_name; else v_name:=’The name is X’; return… Read More »

IF Else Syntax and examples of conditional IF – Then – Else. CREATE OR REPLACE FUNCTION get_result(in p_name varchar(2)) RETURNS varchar AS $$ DECLARE v_name varchar(50); BEGIN if p_name = ‘A’ then v_name:=’The name is A’; return v_name; else v_name:=’The name is X’; return v_name; end if; END; $$ LANGUAGE ‘plpgsql’ IMMUTABLE; Control structures | IF | IF… Read More »

IF / If Then Syntax and examples of conditional IF – Then. CREATE OR REPLACE FUNCTION get_result(in p_name varchar(2)) RETURNS varchar AS $$ DECLARE v_name varchar(50); BEGIN if p_name = ‘A’ then v_name:=’The name is A’; return v_name; end if; END; $$ LANGUAGE ‘plpgsql’ IMMUTABLE; Control structures | IF Else | IF Elsif Else | CASE | LOOP

PostreSQL tutorial – Drop trigger Drop postgresql trigger How to drop trigger, syntax and example. Syntax DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]; Example – Drop postgresql trigger DROP TRIGGER IF EXISTS customers_trigger ON customers CASCADE; DROP TRIGGER IF EXISTS customers_trigger ON customers RESTRICT; CASCADE – drop all objects that depend… Read More »

PostreSQL tutorial – Drop Table Drop postgresql sequence Drop table if exists syntax. Drop sequence. Syntax DROP SEQUENCE [ IF EXISTS ] sequence_name [ CASCADE | RESTRICT ]; Example DROP SEQUENCE sequence_name; Drop postgresql table Syntax DROP TABLE [ IF EXISTS ] table_name [ CASCADE | RESTRICT ]; Example Drop single table: DROP TABLE customers; Drop more than… Read More »

PostreSQL tutorial – Drop function Drop postgresql function Drop function if exists. Syntax and example. Syntax DROP FUNCTION [ IF EXISTS ] function_name ( [ [ argmode ] [ argname ] argtype [, …] ] ) [ CASCADE | RESTRICT ]; Example DROP FUNCTION set_customers(p_first_name varchar, p_last_name varchar); Create Function | Alter Function

PostreSQL tutorial – Create trigger Create postgresql trigger Create Trigger function syntax. Example: before, after, insert, delete, update. Syntax CREATE TRIGGER name { BEFORE | AFTER | INSTEAD OF } ON table_name [FOR [EACH] {ROW | STATEMENT}] EXECUTE PROCEDURE function_name(arguments); Example – Create postgresql trigger function CREATE FUNCTION trg_customers() RETURNS trigger AS $$ BEGIN IF NEW.first_name IS NULL… Read More »

Create function Create postgresql function Create or replace function with parameters. Syntax and examples. CREATE OR REPLACE FUNCTION set_customers(in p_first_name varchar(50), in p_last_name varchar(50)) RETURNS varchar AS $$ DECLARE v_result varchar(250):=’Customer added’; v_count numeric:=0; BEGIN SELECT count(*) into v_count FROM customers WHERE first_name = p_first_name AND last_name = p_last_name; if v_count>0 then v_result:=’Customer allready exist’; return v_result; else… Read More »

PostreSQL tutorial – Create/Alter/Drop View Create postgresql view Create table view, alter and drop view. Syntax and examples. Syntax CREATE [OR REPLACE] [TEMP|TEMPORARY] VIEW view_name [(column_name [, …])] [WITH(view_option_name [= view_option_value] [, … ])] AS view_query; Example CREATE VIEW test_view AS SELECT * FROM customers; Alter postgresql view Syntax ALTER VIEW [ IF EXISTS ] view_name RENAME TO… Read More »