Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to write PostgreSQL storage procedures". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "how to write PostgreSQL storage procedures" together.

Support for commit and rollback

2 Help how to migrate from oracle to postgresql ease

3 Users who fit more traditional databases (ORACLE SQL SERVER MYSQL)

A recent paragraph reflecting on why MYSQL does not have the word stored procedure in large applications summarizes three

MYSQL itself does not support complex query statements (I did not say mysql 8)

2 stored procedures are a collection of SQL statements, dealing with some of the more difficult and troublesome functions of the program, modifying the program more flexible

3. Simple services, complex services can be moved up to the program layer

That does not support the storage process in the end good or not, this is a matter of opinion, if you continue to say it, I guess some people will not be happy.

Ok, our stored procedure back to PG, which also had no stored procedure before PG11, but another alternative way to create function. PG11 supports a relatively complete storage process. However, if the information on the Internet is in Chinese, it belongs to the residual wall. In fact, there are not many English ones. It is estimated that most PG use environments are still at PG 9.4 , PG 10.

About the advantages of stored procedures: PG officials also give the following content (in fact, this is the advantage of stored procedures)

Additional transfers between client and server will be cancelled.

The client does not need or need intermediate results encapsulated or transmitted between client and server.

Multiple rounds of command parsing can be avoided

If you want to return the query contents of a table in PG11 now, from PG 11 to PG12, the currently stored procedure

cannot satisfy this demand. At present, if you want to complete the function to output a table in a section of PLPG SQL

Gather.

CREATE OR REPLACE function insert_data(type varchar(10), content text)

RETURNS TABLE( id int, log_type varchar(10), log_content text, insert_date timestamp)

LANGUAGE plpgsql

AS $$

BEGIN

if( to_regclass('public.log_save') is null ) then

CREATE TABLE public.log_save

(

id serial,

log_type character varying(10),

log_content text,

insert_date timestamp without time zone,

PRIMARY KEY (id)

)

WITH (

OIDS = FALSE,

FILLFACTOR = 80,

autovacuum_enabled = TRUE

);

ALTER TABLE public.log_save OWNER to admin;

end if;

insert into public.log_save (log_type,log_content,insert_date) value

(type,content,now());

RETURN QUERY select * from public.log_save;

END;

$$;

select insert_data ('simple','this is test log');

The function is called by select.

The above is the current way to call the table result set through a function.

By looking at the literature, the current differences between postgresql stored procedures and functions can be summarized as

A stored procedure can include commit rollback.

2 Functions can have a return value output

3 Stored procedures support savepoint functionality

CREATE OR REPLACE PROCEDURE insert_D (type varchar(10),content text)

LANGUAGE plpgsql

AS $$

BEGIN

if( to_regclass('public.log_save') is null ) then

CREATE TABLE public.log_save

(

id serial,

log_type character varying(10),

log_content text,

insert_date timestamp without time zone,

PRIMARY KEY (id)

)

WITH (

OIDS = FALSE,

FILLFACTOR = 80,

autovacuum_enabled = TRUE

);

ALTER TABLE public.log_save OWNER to admin;

end if;

insert into public.log_save (log_type,log_content,insert_date) values (type,content,now());

rollback;

insert into public.log_save (log_type,log_content,insert_date) values (type,content,now());

commit;

--Drop table public.log_save

END;

$$;

call insert_D('a','b');

select * from log_save;

Of course, there are stored procedures that started in POSTGRESQL 11. When looking at some suggestions and materials, there are some places to pay attention to.

1 In creating trigger currently If you want to execute a function or stored procedure after triggering, it is recommended to continue the previous version and continue to use the method of the function

2 PROCEDURE is recommended for large SQL assemblies with transaction breakpoints or where rollback or save points need to be set

Of course, there are also some people who object to POSTGRESQL stored procedures, saying that they are not transactional like other databases. Let's look at the following stored procedures.

CREATE OR REPLACE PROCEDURE check_now()

AS $$

DECLARE

i int;

BEGIN

FOR i in 1.. 5 LOOP

RAISE NOTICE 'It is now: %', now();

PERFORM txid_current();

COMMIT;

PERFORM pg_sleep(0.1);

END LOOP;

END;

$$

LANGUAGE plpgsql;

The results are as follows, questioning where some people think time expressed in a transaction is the same and should not be

Each run of the results below updates the time.

I think it's possible to move things between NOW() and SYSDATE() in MYSQL

PG is in.

We're doing a storage procedure.

CREATE OR REPLACE PROCEDURE check_now1()

AS $$

DECLARE

i int;

BEGIN

FOR i in 1.. 5 LOOP

RAISE NOTICE 'It is now: %', now();

PERFORM txid_current();

RAISE NOTICE 'YES:i%',transaction_timestamp();

PERFORM pg_sleep(0.1);

RAISE NOTICE 'NO:i%',statement_timestamp();

COMMIT;

END LOOP;

END;

$$

LANGUAGE plpgsql;

You can see the difference between NOW() and SYSDATE() in MYSQL

PG stored procedures, if you count the case of functions, in fact PG stored procedures and functions in use and ORACLE , SQL SERVER in function is comparable, but in fact, PG stored procedures may be written in C language, PYTHON, or other accessible languages, we can find that many PG system functions are written in C language, then such language execution speed must be faster than traditional stored procedures, So PG is actually more friendly to programmers in terms of stored procedures. Writing stored procedures may be a troublesome place for programmers, but if it is changed to a language he is familiar with, it will be much easier.

Thank you for reading, the above is the content of "PostgreSQL storage procedure how to write", after the study of this article, I believe that everyone has a deeper understanding of PostgreSQL storage procedure how to write this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.