遞歸查詢與PostgreSQL的擴展函數和類型

遞歸查詢是指在SQL查詢中使用自我引用，以查詢層次結構或遞歸關聯的數據。在PostgreSQL中，有兩種主要方法來實現遞歸查詢：使用公用表表達式（Common Table Expressions，CTEs）和遞歸的WITH子句。

1. 公用表表達式（CTEs）： 公用表表達式是一個命名的臨時結果集，可以在一個查詢中引用。CTEs使得遞歸查詢更加簡潔和易于理解。以下是一個使用遞歸CTE查詢員工及其子員工的示例：

WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, parent_id
  FROM employees
  WHERE parent_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.parent_id
  FROM employees e
  JOIN employee_hierarchy eh ON e.parent_id = eh.id
)
SELECT * FROM employee_hierarchy;

2. 遞歸的WITH子句： PostgreSQL還支持在WITH子句中使用遞歸查詢。這種方法與使用CTEs的方法類似，但不需要為遞歸查詢定義一個臨時結果集的名稱。以下是一個使用遞歸WITH子句查詢員工及其子員工的示例：

WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, parent_id
  FROM employees
  WHERE parent_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.parent_id
  FROM employees e
  JOIN employee_hierarchy eh ON e.parent_id = eh.id
)
SELECT * FROM employee_hierarchy;

PostgreSQL的擴展函數和類型：

1. 擴展函數：擴展函數是用戶自定義的函數，可以在數據庫中執行特定任務。這些函數可以是標量函數、集合函數或表函數。擴展函數可以使用C、Python、Perl等多種編程語言編寫。以下是一個使用C語言編寫的簡單擴展函數示例：

#include "postgres.h"

Datum add_numbers(PG_FUNCTION_ARGS) {
  int32 a = PG_GETARG_INT32(0);
  int32 b = PG_GETARG_INT32(1);
  int32 result = a + b;

  PG_RETURN_INT32(result);
}

2. 擴展類型：擴展類型是用戶自定義的數據類型，可以在數據庫中存儲和處理特定類型的數據。這些類型可以是基本數據類型（如整數、浮點數、字符串等）或復雜數據類型（如數組、記錄、枚舉等）。擴展類型可以使用C、Python、Perl等多種編程語言編寫。以下是一個使用C語言編寫的簡單擴展類型示例：

#include "postgres.h"

typedef struct {
  int32 id;
  char name[50];
} Person;

typedef struct {
  Person *persons;
  int32 count;
} PersonList;

/* 創建Person類型的函數 */
Person *create_person(int32 id, const char *name);

/* 創建PersonList類型的函數 */
PersonList *create_person_list(int32 count);

總之，遞歸查詢是PostgreSQL中查詢層次結構數據的有效方法，而擴展函數和類型則為用戶提供了更多的自定義功能。