CODEWITHSUNDEEP
sqlfunctionpostgresql-9.2
Zapnologica
Zapnologica

Reputation: 22566

PostgreSQL SELECT attributes from a Custom type in a function

Here is my type:

CREATE TYPE occupant AS (name text, title title);

And here is my table:

CREATE TABLE office (id INTEGER DEFAULT NEXTVAL('venue_id_seq'),occupant occupant) INHERITS(venue);

and then this is my function:

 CREATE or REPLACE FUNCTION occupantName(id int) RETURNS text AS 
$$
  SELECT occupant.tile + occupant.name FROM office as v WHERE id = v.id;
$$ LANGUAGE SQL;

It is giving me this error:

ERROR:  missing FROM-clause entry for table "ocupant"
LINE 15:       SELECT ocupant.tile + ocupant.name FROM office as v WH...

Upvotes: 6

Views: 5554

Answers (1)

roman
roman

Reputation: 117540

You have to use parenthesis around occupant to access composite type fields:

CREATE or REPLACE FUNCTION occupantName(id int) RETURNS text AS 
$$
  SELECT (occupant).tile || (occupant).name FROM office as v WHERE id = v.id;
$$ LANGUAGE SQL;

Upvotes: 8

Related Questions