sql - 如何解决 PostgreSQL 中缺少 FROM 子句的问题？

Question

我在 PosgreSQL 中创建了以下表和类型。

CREATE TYPE WordTy AS (
 id VARCHAR(20)  
);

CREATE TYPE PageTy AS (
  url VARCHAR(100),
  title VARCHAR(60),
  data_lettura DATE
);

CREATE TYPE OccurrenceTy AS (
  word WordTy,
  page PageTy,
  occurrences_number INTEGER
  );

CREATE TABLE Page OF PageTy(PRIMARY KEY(url));

CREATE TABLE Word OF WordTy(PRIMARY KEY(id));

CREATE TABLE Occurrence OF OccurenceTY;

我想知道包含给定单词的所有页面。
当我运行此查询时:

SELECT occ.page.title as page_title, occ.page.url as url_page FROM 
  Occurrence occ WHERE occ.id=1 

输出是:

ERROR:  missing FROM-clause entry for table "page"

我哪里错了？

Answer 1

参见 Accessing Composite Types .

您需要使用括号:(occ.page).title。

---

您的 SQL 中也有一些拼写错误:

CREATE TABLE Occurrence OF OccurenceTY;

应该是

CREATE TABLE Occurrence OF OccurrenceTy;

FROM Occurrences occ

应该是

FROM Occurrence occ

和

WHERE occ.id=1

应该是

WHERE (occ.word).id='1'

---

最终查询:

SELECT (occ.page).title as page_title, (occ.page).url as url_page FROM 
  Occurrence occ WHERE (occ.word).id='1'