Pl sql trigger inserting deleting updating

Posted by / 20-Aug-2017 06:18

= ' ' 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 "%".

||

= ' ' 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".

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.

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.

~ 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 "%".

||

= ' ' 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".

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.

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.

, ' ',''); 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 "%".

||

= ' ' 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".

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.

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.

; 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".

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.

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 solution is to use an untrusted language, like pl/perl or pl/python.The following code is an example of bad programming style (I found this code on net): create or replace function check_phone_number (text) returns boolean as $$ declare _str alias for

The solution is to use an untrusted language, like pl/perl or pl/python.

The following code is an example of bad programming style (I found this code on net): create or replace function check_phone_number (text) returns boolean as $$ declare _str alias for $1; _char text; i int4; begin i=1; while true loop if length (_str) !

= 16 then exit; end if; _char := substring(_str from i for 1); if _char !

PL/pg SQL is a simple ADA-based programming language with SQL support.

This language was significantly inspired by Oracle's PL/SQL language.

||

The solution is to use an untrusted language, like pl/perl or pl/python.The following code is an example of bad programming style (I found this code on net): create or replace function check_phone_number (text) returns boolean as $$ declare _str alias for $1; _char text; i int4; begin i=1; while true loop if length (_str) != 16 then exit; end if; _char := substring(_str from i for 1); if _char !PL/pg SQL is a simple ADA-based programming language with SQL support.This language was significantly inspired by Oracle's PL/SQL language.

; _char text; i int4; begin i=1; while true loop if length (_str) != 16 then exit; end if; _char := substring(_str from i for 1); if _char !PL/pg SQL is a simple ADA-based programming language with SQL support.This language was significantly inspired by Oracle's PL/SQL language.

pl sql trigger inserting deleting updating-55pl sql trigger inserting deleting updating-14pl sql trigger inserting deleting updating-7

In Postgre SQL, stored procedures can be created using any of these languages: SQL, Perl, Python, TCL, and PL/pg SQL.