Take the 2-minute tour ×

I want to create a function that returns a type that I made, but when I execute it, it says that the type doesn't exists. I assume that's cause it doesn't know about the custom type.


CREATE TYPE building_code AS ENUM ('IT','EMS','HSB','ENG');


 CREATE FUNCTION roomCode(id int ) RETURNS building_code AS 
 SELECT building_code FROM venue as v WHERE id = v.id;
share|improve this question
Missing: Postgres version, verbatim error message, table definition. –  Erwin Brandstetter Aug 14 '13 at 15:23
you've called your column name exactly like you type - I'd suggest to avoid that –  Roman Pekar Aug 14 '13 at 15:25
add comment

1 Answer

up vote 1 down vote accepted

This should just work. The enum should not be a problem. Tested with Postgres 9.1 and 9.2

CREATE TEMP TABLE venue (id int PRIMARY KEY, building_code building_code);

CREATE OR REPLACE FUNCTION room_code(_id int) --!
  RETURNS building_code AS 
SELECT building_code FROM venue v WHERE v.id = $1 -- !

SELECT * FROM room_code(1);

Except ...

  • In versions before 9.2 you can only use positional (numeric) parameters ($1) in SQL functions (unlike plpgsql functions).
    In 9.2+ the column name would take precedence, so that the WHERE clause of your original code would always be TRUE and all rows would qualify - except that your function only returns the first, since it does not return a SETOF building_code.
    Either rename your parameter or use positional parameter or, preferably, both.
    If you must use conflicting parameter names, you can override the preference by using the function name to qualify the parameter. Like:

    ... WHERE v.id = room_code.id
  • You shouldn't use the type name as column name.

  • You should not use unquoted mixed case names like roomCode, which will be folded to lower case, unless you double-quote: "roomCode".

->SQLfiddle with 3 variants

share|improve this answer
does select ... from venue where v.id = id will treat unaliased id as parameter or as column from venue? –  Roman Pekar Aug 14 '13 at 15:29
@RomanPekar: Good point. You can't use the parameter name at all in versions before 9.2. Only positional parameters. In 9.2+ the column name takes precedence. I amended my answer. –  Erwin Brandstetter Aug 14 '13 at 15:34
thanks, @ErwinBrandstetter, so you either use positional or rename parameters. Will know :) –  Roman Pekar Aug 14 '13 at 15:38
+1 aha, positional OR prefixed with function name :) –  Roman Pekar Aug 14 '13 at 15:55
add comment

Your Answer


