Use PL/pgsql to write PostgreSQL stored procedures

Today, I learned how to use PL/pgsql to write the PostgreSQL storage process. There is really little information on the Internet. The only thing I can find is to copy it. I 'd like to copy the PostgreSQL documentation, let's talk about the problem solved today. I hope it will help others.
The problem is that there is a message table:
Create Table message
(
Id int8 not null,
Receiveuserid int8,
Senduserid int8,
Receivedelete bool default false,
Senddelete bool default false,
......
Constraint usermessage_pkey primary key (ID)
)
Other fields are omitted. senduserid is the user ID of the message sender. If senddelete is set to true, the message is deleted by the sender. As for receive, I don't need to mention it. A message can be deleted from the table only when it is deleted by both the sender and recipient. (This is obvious. Otherwise, after the recipient deletes a message, the message cannot be found in the sender's "sender ).
Therefore, when deleting a message (maybe multiple messages), you need to make various judgments (whether it is the sender? Is it the recipient? Are you sure you want to delete it ?), It is obviously difficult to use a single SQL statement to complete this job (of course, it can be achieved without considering the efficiency of the nested loop SELECT statement), so we have to write the stored procedure.
By the way, we will introduce the common PL/pgsql structure and Syntax:

• Structure

PL/pgsql is a block structure language. It is more convenient to use pgadmin III to create a function and enter some parameters. Basically:

Create or replace function name (parameter 1, [integer int4, integer array _ int4,...])
Returns return value type
$ Body $
Declare
Variable Declaration
Begin
Function body
End;
$ Body $
Language 'plpgsql' volatile;

• Variable type

In addition to the built-in variable types of postgresql, there are also common record, indicating a record.

• Assignment

Assignment is a bit like Pascal: "variable: = expression ;"
The strange thing is that the connection string is "|", for example, SQL: = 'select * from' | table | 'where... ';

• Judgment

The judgment is similar to that of VB:
If condition then
...
Elseif condition then
...
Else
...
End if;

• Loop

There are several statements for loop:
While expression Loop
Statements
End loop;
There is also a common one: (from 1 loop to 9 can be written as for I in 1 .. 9 loop)
For name in [reverse] expression... expression Loop
Statements
End loop;

• Others

There are also several common functions:
Select into record ...; Grant the select result to the record variable (record type)
Perform query; indicates that the query is executed and the result is discarded.
Execute SQL; indicates that the SQL statement can be executed dynamically (especially when the SQL statement is passed in by parameters)

Finally, paste the stored procedure to solve the problem above:

1. Create or replace function message_deletes (IDs "varchar", userid int8)
2. Returns int4
3. $ Body $
4. Declare
5. R record;
6. Del bool;
7. Num int4: = 0;
8. SQL "varchar ";
9. Begin
10. SQL: = 'select ID, receiveuserid, senduserid, senddelete, receivedelete from message where ID in ('| IDs | ')';
11. For R in Execute SQL Loop
12. DEL: = false;
13. If R. receiveuserid = userid and R. senduserid = userid then
14. DEL: = true;
15. Elseif R. receiveuserid = userid then
16. If R. senddelete = false then
17. Update message set receivedelete = true where id = R. ID;
18. Else
19. DEL: = true;
20. End if;
21. Elseif R. senduserid = userid then
22. If R. receivedelete = false then
23. Update message set senddelete = true where id = R. ID;
24. Else
25. DEL: = true;
26. End if;
27. End if;
28. If del then
29. Delete from message where id = R. ID;
30. Num: = num + 1;
31. End if;
32. End loop;
33. Return num;
34. End;
35. $ Body $
36. Language 'plpgsql' volatile;

Test use: