SQL Function (plpgsql) Type-Defined Outputs?

Question

I've been defining the output of methods as...

CREATE TABLE person AS (
  id serial PRIMARY KEY,
  name text NOT NULL,
  fingerprint text
);

CREATE TYPE person_output AS (
  name text
);

CREATE OR REPLACE FUNCTION my_schema.create_person(_name text) 
  RETURNS person_output AS $$
  DECLARE
    output person_output;
  BEGIN
    INSERT INTO person (name) VALUES (_name) RETURNING name INTO person_output;
    RETURN person_output;
  END;
$$ LANGUAGE plpgsql;

I'm wondering if this rule of creating a type for what the function should output is standard within industry? Or, would it be better to create a view and just return 1 or raised errors depending if it succeeded or failed, and then calling the view in server-side python code to return?

I could see type-based outputs being good for creating "upgrades" like, CREATE TYPE person_output_v002 for the next-version, and creating a template for adapting, but then again, that wouldn't be needed if there were views.

The only reason I have decided not to use views is because I have things like groups of people and I'd like to create views for the groups, but I'd need to add a WHERE group_id = _GROUP_ID type of thing when selecting the view which I think isn't possible.

Might there be a way to select VIEWS with additional query parameters?

---- UPDATE ------

When there are certain outputs like current_user repeated as RETURN _current_user_output across multiple FUNCTIONs, is it a good idea to create one TYPE current_user_output so that we can be sure all FUNCTIONs which output the current_user are returning the same thing?

Answer 1

You do not need a row type for that at all. Not even a PL/pgSQL function. Simplify:

CREATE OR REPLACE FUNCTION my_schema.create_person(_name text) 
  RETURNS text AS
$func$
   INSERT INTO person (name)
   VALUES (_name)
   RETURNING name;
$func$ LANGUAGE sql;

There is no rule of creating a type for what the function should output. The requirement is to declare the return type, and that's required by SQL which demands to know the type at execution time. To return multiple columns, you don't have to create a type. You can use RETURNS TABLE:

RETURNS TABLE (col1 int, col2 text, ...)

I suggest to read these two chapters of the manual:

• Returning From a Function
• CREATE FUNCTION

And you can use the readily defined row type of existing tables or polymorphic types:
Refactor a PL/pgSQL function to return the output of various SELECT queries

Added question:

... so that we can be sure all FUNCTIONs which output the current_user are returning the same thing?

If your functions just return text, you can be just as sure. It might make sense for row-types. Then it can be useful to define a composite type to share. Complicates maintenance. If you want to change the return type, you have to change alls functions at once. But that may be as intended ...