PostgreSQL SELECT attributes from a Custom type in a function

Question

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...

Zapnologica · Accepted Answer · 2013-08-16 11:21:54Z

8

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;

edited Aug 16, 2013 at 11:21

Zapnologica

22.6k44 gold badges175 silver badges260 bronze badges

answered Aug 15, 2013 at 14:43

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zapnologica Over a year ago

Just to add to your answer I also had to use TEXTCAT(,) instead of the + sign

roman Over a year ago

or || concatenation operator

Purusottam Over a year ago

@RomanPekar Thanks for your answer. This saved me countless hours of debugging.

Collectives™ on Stack Overflow

PostgreSQL SELECT attributes from a Custom type in a function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related