Use PL/pgsql to write PostgreSQL stored procedures

Source: Internet
Author: User
Tags postgresql

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.