postgresql - Postgresql 中的存储过程中的表别名不存在架构

Question

架构“tb”不存在。对于 tb.DOWName。此查询中有 3 个表。

存储过程:

CREATE OR REPLACE PROCEDURE MeetingList ( INT, INT, VARCHAR(25)) 
LANGUAGE plpgsql
AS $$
BEGIN   
    SELECT ListID,
    a.DOWID, 
    tb.DOWName 'Day',
    a.aTimeID,
    c.aTime 'Time',
    Town,
    GroupName,
    Information,Location,Type
    FROM list  a
    INNER JOIN dow tb ON a.DOWID = tb.DOWID
    INNER JOIN atime AS c ON a.aTimeID = c.aTimeId
    WHERE (a.DOWID = $1 OR $1 IS NULL)
    AND (a.aTimeID = $2 or $2 IS NULL)
    AND (Town = $3 or $3 IS NULL)
    ORDER BY a.DOWID,a.aTimeID, Town;
END;
$$;

查询结果:

deig=# CALL MeetingList(1,60,'Ellsworth');
ERROR:  schema "tb" does not exist
LINE 3: tb.DOWName 'Day',
        ^
QUERY:  SELECT ListID,
a.DOWID, 
public.tb.DOWName 'Day',
a.aTimeID,
c.aTime 'Time',
Town,
GroupName,
Information,Location,Type
FROM list  a
INNER JOIN dow tb ON a.DOWID = tb.DOWID
INNER JOIN atime AS c ON a.aTimeID = c.aTimeId
WHERE (a.DOWID = $1 OR $1 IS NULL)
AND (a.aTimeID = $2 or $2 IS NULL)
AND (Town = $3 or $3 IS NULL)
ORDER BY a.DOWID,a.aTimeID, Town
CONTEXT:  PL/pgSQL function meetinglist(integer,integer,character 

varying) line 3 at SQL statement

我正在使用数据库“deig”以用户“bart”的身份从 psql 运行它。有 3 个表:

 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | atime | table | bart
 public | dow   | table | bart
 public | list  | table | bart```

我一直在 SQL Express 中使用这个存储过程，但在 Postgresql 11 中没有成功。

Answer 1

Identifiers and aliases应该用 " 引用而不是 '

SELECT ListID,
       a.DOWID, 
       tb.DOWName AS "Day",
       a.aTimeID,
       c.aTime AS "Time",
 -- ...

db<>fiddle demo