Declare and return a custom type in PostgreSQL function

Question

I found this article: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions and I'm trying to use it as an example for my function. I am selecting different columns from different tables, and trying to return a set of records. Here's my code: ``` CREATE OR REPLACE FUNCTION get_details_for_widget(widgetid integer) RETURNS SETOF widgetdetails AS $BODY$ DECLARE rec widgetdetails %rowtype; BEGIN FOR rec IN ( SELECT widget_details.id, widget_details.contact_id, widget_details.priority, widget_owner.contact FROM widget_details, widget_owner WHERE widget_details.rid=widgetid AND widget_details.active_yn = 't' AND widget_owner.id=widget_details.contact_id Order by widget_details.priority ASC) LOOP RETURN NEXT rec; END LOOP; END; $BODY$ LANGUAGE plpgsql; ``` When I try to compile this code, I get the error the the type "widgetdetails" does not exist. In line with the example in the wiki, I changed my logic to look like this: ``` CREATE OR REPLACE FUNCTION get…

Accepted Answer

The syntax you tried for the `RETURNS` clause is foreign to Postgres.

Your code is much more complicated than it needs to be. You don't need PL/pgSQL for such a simple function. Use a plain SQL function instead:

```
CREATE OR REPLACE FUNCTION get_details_for_widget(widgetid integer)
  RETURNS TABLE (id int, contact_id int, priority int, contact text)
  LANGUAGE sql AS
$func$
   SELECT d.id, d.contact_id, d.priority, o.contact
   FROM   widget_details d
   JOIN   widget_owner   o ON o.id = d.contact_id
   WHERE  d.rid = widgetid   -- where does widgetid come from?
   AND    d.active_yn = 't'
   ORDER  BY d.priority;
$func$
```

`RETURNS TABLE` defines an ad-hoc row type. Adapt column types to your actual (undisclosed) table definitions. (This part works the same for PL/pgSQL functions.)

Use a proper `JOIN` condition for better readability.

Simplify your query with table aliases.

Use the data type `boolean` for `widget_details.active_yn`.

Boolean values

I would advice to use `true` / `false` instead of the string literals 't' / 'f' for data input into a boolean column. The manual:

The key words `TRUE` and `FALSE` are the preferred (SQL-compliant) usage.

In a `WHERE` clause, every expression is evaluated to a `boolean` result. `true` qualifies, `false` or `null` do not. For a `boolean` type, simplify this:

```
   AND    d.active_yn = TRUE
```

to just:

```
   AND    d.active_yn
```

Declare and return a custom type in PostgreSQL function

Solution

Problem

Related problems