CODEWITHSUNDEEP
sqlpostgresql
Davide Scaraggi
Davide Scaraggi

Reputation: 57

How to solve missing FROM clause in PostgreSQL?

I've created the following tables and types in 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;

I want to know all the pages that contain a given word.
When I run this query:

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

The output is:

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

Where am I wrong?

Upvotes: 1

Views: 143

Answers (1)

Adam
Adam

Reputation: 5599

See Accessing Composite Types.

You need to use brackets: (occ.page).title.

You also have some typos in your SQL:

CREATE TABLE Occurrence OF OccurenceTY;

should be

CREATE TABLE Occurrence OF OccurrenceTy;

FROM Occurrences occ

should be

FROM Occurrence occ

and

WHERE occ.id=1

should be

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

Final query:

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

Upvotes: 2

Related Questions