How To Dev

Introduction This is a data storage mechanism used to solve the problem of temporary data storage. Although it is called a Temporary Table , this is actually a real table created in the database just like other normal tables. Characteristics The scope of existence of a Temporary Table is throughout that Session, until you disconnect. It is stored like a real table and can be written to disk if it exceeds temp_buffers. It has all the properties of a physical table, such as the ability to create indexes to speed up data queries, run ANALYZE or view the table structure. Its lifecycle only lasts throughout that Session, when you disconnect from the database, this temporary table will be automatically deleted, or you can also actively execute DROP TABLE. Because it exists throughout the session, it can be called for use multiple times in different statements and also within Functions or Stored Procedures. Use cases The following are appropriate cases to use a Temporary Table , including V...

Introduction When using older versions of Postgres, only Functions were available. However, from version 11 onwards, Postgres has officially added support for Procedures to address the limitations that Functions could not overcome. While their implementations are quite similar, they possess fundamental core differences: Transaction Control The biggest difference is that a Procedure allows you to have full control over transactions. You can use COMMIT or ROLLBACK directly inside the body of the procedure. This is extremely useful when you need to process large amounts of data in batches without worrying about memory overflow or locking tables for too long. Conversely, a Function cannot contain COMMIT or ROLLBACK. The entire Function must execute within a single transaction. If a command at the end of the function fails, all previous commands are completely rolled back. Return Value Using a Function requires returning a certain value, even if it is just void, a single value, or a TABLE d...