postgresql - postgres #我的表语法错误

Question

以下代码显示 postgres sql 中 #my table seq 附近的语法错误。

IF (p_BusinessID = '')
    THEN
        SELECT RANK() OVER(ORDER BY SSO) SNO,Business, SSO, DisplayName, UPPER(ExServerName) ExServerName, POP, IMAP, EWS, DisplayMonth, DisplayYear 
        FROM "POP_IMAP_EWS_Data"
        WHERE DisplayYear = COALESCE(p_YearID, DisplayYear) AND MonthID = COALESCE(p_MonthID, MonthID) AND SSO = COALESCE(p_SSO, SSO);
    ELSE
       CREATE SEQUENCE #myTable_seq;

       CREATE TABLE #myTable 
       (
                     ID                                      INT DEFAULT NEXTVAL ('#myTable_seq'),
                     Item                    VARCHAR(100), 
       );

       INSERT INTO #myTable(Item)
       SELECT RTRIM(LTRIM(Item)) FROM SplitString(;p_BusinessID, ',');

       SELECT RANK() OVER(ORDER BY SSO) SNO, A.BusinessID, Business, SSO, DisplayName, UPPER(ExServerName) ExServerName, POP, IMAP, EWS, DisplayMonth, DisplayYear 
       FROM "POP_IMAP_EWS_Data"A
       INNER JOIN #myTable B ON B.Item = A.BusinessID
       WHERE DisplayYear = COALESCE(p_YearID, DisplayYear) AND MonthID = COALESCE(p_MonthID, MonthID) AND SSO = COALESCE(p_SSO, SSO);

谁能告诉我为什么？

Answer 1

使用 # 前缀来指示临时表是特定于 SQL Server(也可能是 Sybase)的。 PostgreSQL 要你说 create temporary table ... :

create temporary table mytable (
    id serial primary key,
    item varchar(100)
);

此外，您通常只需将 id 列设为 serial 列，让 PostgreSQL 处理序列而不是自己将其全部连接起来，所以我更改了它还有；使用 serial 还负责设置序列的所有者。