Traversing values in a table using a FOR loop procedure_demo=# CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql AS $$ Procedure_demo=# call genre_id_exception() ĬONTEXT: PL/pgSQL function genre_id_exception() line 6 at RAISEħ. Procedure_demo$# RAISE EXCEPTION 'Maximum of GenreId is : %', id USING HINT = 'Test For Raising exception.' Raising exceptions procedure_demo=# CREATE OR REPLACE PROCEDURE genre_id_exception() LANGUAGE plpgsql AS $$ Procedure_demo$# RAISE INFO 'value of warn : % at %: ', warn, now() Procedure_demo$# RAISE WARNING 'value of warn : % at %: ', warn, now() Procedure_demo$# RAISE NOTICE 'value of warn : % at %: ', warn, now() Raising notices, warnings, and INFO messages procedure_demo=# CREATE OR REPLACE PROCEDURE raise_warning() AS $$ Procedure_demo$# RAISE NOTICE 'Maximum of GenreId is : %', id ĥ. Procedure_demo$# select max("GenreId") into id from public."Genre" Procedure_demo$# id "Genre"."GenreId"%type Using column data types procedure_demo=# CREATE OR REPLACE PROCEDURE genre_id_max() LANGUAGE plpgsql AS $$ Here we can see that data before commit is available, but then the data that is not committed and is rolled back gets dropped from the database.Ĥ. Procedure_demo=# select relname from pg_class where relname like '%test%' Procedure_demo=# call control_transaction() Procedure_demo$# INSERT INTO test2 VALUES (1)
Procedure_demo$# CREATE TABLE test2 (id int) Procedure_demo$# INSERT INTO test1 VALUES (1) Procedure_demo$# CREATE TABLE test1 (id int) Using transaction control procedure_demo=# CREATE OR REPLACE PROCEDURE control_transaction() NOTICE: Procedure Parameter: This is my test caseģ. Procedure_demo=# call display_message('This is my test case') Procedure_demo$# RAISE NOTICE 'Procedure Parameter: %', msg Displaying a message on the screen procedure_demo=# CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT) Procedure_demo=# select * from public."Genre" where "GenreId" = 26 Ģ. Procedure_demo=# CALL genre_insert_data(26,'Pop') Procedure_demo$# INSERT INTO public."Genre" VALUES ("GenreId", "Name") Inserting data using a procedure procedure_demo=# CREATE OR REPLACE PROCEDURE genre_insert_data("GenreId" integer, "Name" character varying) Here are ten examples of stored procedures that can be useful in different situations.ġ. Traversing values in a table using a FOR loopĪ stored procedure is a set of structured queries and statements such as control statements and declarations. Raising notices, warnings, and INFO messagesħ. SUMMARY: This article provides ten examples of stored procedures in PostgreSQL.ĥ.
0 kommentar(er)
