Pl sql trigger inserting deleting updating

= ' ' then exit; end if; i:=i 1; _char := substring(_str from i for 1); if _char CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT

= ' ' then exit; end if; i:=i 1; _char := substring(_str from i for 1); if _char CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT $1 ~ e'^\\ \\d\\ \\d \\d \\d$' THEN RAISE EXCEPTION 'Wrong formated string "%".Expected format is 999 999 999 999'; END IF; RETURN true; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION normalise_phone_number(text) RETURNS text AS $$ DECLARE aux text := translate($1, ' ',''); BEGIN IF aux ~ e'^\\ \\d$' THEN RETURN substring(aux FROM 2); ELSEIF aux ~ e'^\\d$' THEN RETURN aux; ELSE RAISE EXCEPTION 'Unexpected format of phone number "%".', $1; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; postgres=# select normalise_phone_number(' 420 222 222 222'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 422'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 420 22 222222'); ERROR: Unexpected format of phone number " 420 22 222222".

||

= ' ' then exit; end if; i:=i 1; _char := substring(_str from i for 1); if _char CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT $1 ~ e'^\\ \\d\\ \\d \\d \\d$' THEN RAISE EXCEPTION 'Wrong formated string "%".

Expected format is 999 999 999 999'; END IF; RETURN true; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION normalise_phone_number(text) RETURNS text AS $$ DECLARE aux text := translate($1, ' ',''); BEGIN IF aux ~ e'^\\ \\d$' THEN RETURN substring(aux FROM 2); ELSEIF aux ~ e'^\\d$' THEN RETURN aux; ELSE RAISE EXCEPTION 'Unexpected format of phone number "%".', $1; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; postgres=# select normalise_phone_number(' 420 222 222 222'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 422'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 420 22 222222'); ERROR: Unexpected format of phone number " 420 22 222222".

~ e'^\ \d\ \d \d \d$' THEN RAISE EXCEPTION 'Wrong formated string "%".

Expected format is 999 999 999 999'; END IF; RETURN true; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION normalise_phone_number(text) RETURNS text AS $$ DECLARE aux text := translate(

= ' ' then exit; end if; i:=i 1; _char := substring(_str from i for 1); if _char CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT $1 ~ e'^\\ \\d\\ \\d \\d \\d$' THEN RAISE EXCEPTION 'Wrong formated string "%".Expected format is 999 999 999 999'; END IF; RETURN true; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION normalise_phone_number(text) RETURNS text AS $$ DECLARE aux text := translate($1, ' ',''); BEGIN IF aux ~ e'^\\ \\d$' THEN RETURN substring(aux FROM 2); ELSEIF aux ~ e'^\\d$' THEN RETURN aux; ELSE RAISE EXCEPTION 'Unexpected format of phone number "%".', $1; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; postgres=# select normalise_phone_number(' 420 222 222 222'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 422'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 420 22 222222'); ERROR: Unexpected format of phone number " 420 22 222222".

||

= ' ' then exit; end if; i:=i 1; _char := substring(_str from i for 1); if _char CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT $1 ~ e'^\\ \\d\\ \\d \\d \\d$' THEN RAISE EXCEPTION 'Wrong formated string "%".

Expected format is 999 999 999 999'; END IF; RETURN true; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION normalise_phone_number(text) RETURNS text AS $$ DECLARE aux text := translate($1, ' ',''); BEGIN IF aux ~ e'^\\ \\d$' THEN RETURN substring(aux FROM 2); ELSEIF aux ~ e'^\\d$' THEN RETURN aux; ELSE RAISE EXCEPTION 'Unexpected format of phone number "%".', $1; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; postgres=# select normalise_phone_number(' 420 222 222 222'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 422'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 420 22 222222'); ERROR: Unexpected format of phone number " 420 22 222222".

, ' ',''); BEGIN IF aux ~ e'^\ \d$' THEN RETURN substring(aux FROM 2); ELSEIF aux ~ e'^\d$' THEN RETURN aux; ELSE RAISE EXCEPTION 'Unexpected format of phone number "%".',

= ' ' then exit; end if; i:=i 1; _char := substring(_str from i for 1); if _char CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT $1 ~ e'^\\ \\d\\ \\d \\d \\d$' THEN RAISE EXCEPTION 'Wrong formated string "%".Expected format is 999 999 999 999'; END IF; RETURN true; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION normalise_phone_number(text) RETURNS text AS $$ DECLARE aux text := translate($1, ' ',''); BEGIN IF aux ~ e'^\\ \\d$' THEN RETURN substring(aux FROM 2); ELSEIF aux ~ e'^\\d$' THEN RETURN aux; ELSE RAISE EXCEPTION 'Unexpected format of phone number "%".', $1; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; postgres=# select normalise_phone_number(' 420 222 222 222'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 422'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 420 22 222222'); ERROR: Unexpected format of phone number " 420 22 222222".

||

= ' ' then exit; end if; i:=i 1; _char := substring(_str from i for 1); if _char CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT $1 ~ e'^\\ \\d\\ \\d \\d \\d$' THEN RAISE EXCEPTION 'Wrong formated string "%".

Expected format is 999 999 999 999'; END IF; RETURN true; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION normalise_phone_number(text) RETURNS text AS $$ DECLARE aux text := translate($1, ' ',''); BEGIN IF aux ~ e'^\\ \\d$' THEN RETURN substring(aux FROM 2); ELSEIF aux ~ e'^\\d$' THEN RETURN aux; ELSE RAISE EXCEPTION 'Unexpected format of phone number "%".', $1; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; postgres=# select normalise_phone_number(' 420 222 222 222'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 422'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 420 22 222222'); ERROR: Unexpected format of phone number " 420 22 222222".

; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; postgres=# select normalise_phone_number(' 420 222 222 222'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 422'); normalise_phone_number ------------------------ 420222222222 (1 row) postgres=# select normalise_phone_number(' 420 22 222222'); ERROR: Unexpected format of phone number " 420 22 222222".

-- non recursion form CREATE OR REPLACE FUNCTION public.psqlfibnr(n integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE STRICT AS $function$ DECLARE prev1 int = 0; prev2 int = 1; result int = 0; BEGIN FOR i IN 1..n LOOP result := prev1 prev2; prev2 := prev1; prev1 := result; END LOOP; RETURN result; END; $function$ -- recursion form CREATE OR REPLACE FUNCTION public.psqlfibr(n integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE STRICT AS $function$ BEGIN IF n Speed of recursion calls in PL/pg SQL is not comparatable with recursion optimized languages like Javascript. SQL procedures will be faster everytime when we can use a integrated functionality.

I use a bubble sort as example - but it can be quick sort too.

You can implement it in PL/pg SQL without any problem, but your code will be always slower (sometimes significantly slower) than simple SQL function that uses integrated quicksort: PL/pg SQL does not create new types or its own functions. PL/pg SQL functions can contains almost all of SQL statements.

It has a FOR statement for iteration over SELECT's result. Authors were surely inspired by the PL/SQL language, which is the native programming language for Oracle RDBMS, so it’s not difficult to convert stored procedures from Oracle to Postgre SQL or back.

It is possible to use Pl/pg SQL in Postgre SQL for implementation of own aggregates or normal functions, operators, and for the implementation trigger handlers.

Each new version of Postgre SQL improves the possibilities of stored procedures, so CREATE FUNCTION statement are enhanced in any Postgre SQL version too.

postgres=# select normalise_phone_number('724 191 000'); normalise_phone_number ------------------------ 724191000 (1 row) Due internal design principles PL/pg SQL should not be well optimized for recursion calls.

PL/pg SQL supports recursion, and for not too deeps calls can provide enough to satisfy sb performance, but it is very slow for deep recursion. It is perfect example how don't use PL/pg SQL ever.

The function generate_series is an SRF function generating an incrementing set of values over the entered interval. When you’re not satisfied with the speed of a function, try to use the following trick by converting a SRF function – replace the part of string concation by RETURN NEXT command.

Tags: , ,