PostgreSQL implements batch execution of SQL in file

PostgreSQL sql is placed in the file, and then logged into the database and executed in batches.

1. Create test sql:

vi

Insert: guess each SQL statement is used; separated, multiple functions; it will also be automatically recognized.

create table tb1(id integer);
insert into tb1 select generate_series(1,10);
select * from tb1;
delete from
tb1 where id<3;
select * from tb1;

2. Put it in ./src/postgresql-9.3.5/src/tutorial (./src/postgresql-9.3.5/src/tutorial is a directory that PostgreSQL automatically recognizes, of course it can also be placed in any directory, such as /home/postgres/)

3. Switch user login

su postgres
psql postgres

4. Execution: When entering \i, the file under ./src/postgresql-9.3.5/src/tutorial will be automatically detected. PostgreSQL test examples are also placed in this directory.

postgres=# \i  (\i /home/postgres/)
 id | name 
----+------
 1 | join
 2 | join
 3 | join
 4 | join
 5 | join
 6 | join
 7 | join
 8 | join
 9 | join
 10 | join
(10 rows)
 
CREATE TABLE
INSERT 0 10
 id 
----
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
(10 rows)
 
DELETE 2
 id 
----
 3
 4
 5
 6
 7
 8
 9
 10
(8 rows)
 
postgres=# 

postgres=# \d tb1 
   Table "public.tb1"
 Column | Type  | Modifiers 
--------+---------+-----------
 id   | integer | 

The second example:

vi :

Write a function:

create function func1()returns void as $$
declare
begin
delete from person where id>5;
delete from tb1 where id>5;
end
$$language plpgsql;
 
select func1();

Switch to postgres, and execute after logging in:

Before execution:

postgres=# select * from person ;
 id | name 
----+------
 1 | join
 2 | join
 3 | join
 4 | join
 5 | join
 6 | join
 7 | join
 8 | join
 9 | join
 10 | join
(10 rows)
 
postgres=# select * from tb1 ;
 id 
----
 3
 4
 5
 6
 7
 8
 9
 10
(8 rows)

implement:

postgres=# \i  
CREATE FUNCTION
 func1 
-------
 
(1 row)

After execution:

postgres=# select * from person ;
 id | name 
----+------
 1 | join
 2 | join
 3 | join
 4 | join
 5 | join
(5 rows)
 
postgres=# select * from tb1 ;
 id 
----
 3
 4
 5
(3 rows)
 
postgres=# 

5. You can also use the psql command to execute

pslq -d postgres -U postgres -f /home/postgres/

Additional: PostgreSQL - Run SQL files with psql

For pre-written SQL files, such as /home/user1/, there are two ways to run this SQL file.

Method 1:

Execute SQL files after connecting to db

First connect to the corresponding db through psql:

psql -d db1 -U userA

Then enter the password, enter:

\i /pathA/

There is a problem here. If you write the path separator in the path of the SQL file as \, you will report an error saying Permission denied.

The file path here must use the path separator/ under the Linux platform, otherwise an error will be reported.

Method 2:

Execute SQL files directly through the psql command

This method does not require logging into the database first, just use one command:

psql -d db1 -U userA -f /pathA/

Then enter the password to execute the SQL file into the corresponding db.

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.