Return type of JOINing stored procedure - PostgreSQL

Question

Is it possible to express return type of stored procedure that returns joined tables using table type of one of joined tables?

My stored procedure looks like this:

CREATE or REPLACE FUNCTION selectJoin()
RETURNS SETOF ???
AS $$
BEGIN
    RETURN QUERY SELECT t1.*, t2.name
                 FROM t1, t2
                 WHERE t1.t2_id = t2.id;
END; $$
LANGUAGE plpgsql;

Table t1 has many (approx 70) columns so I'd like to somehow reuse the t1 type in RETURNS clause. Something like (t1, varchar(30)) or composite type inheritace. Is is possible?

PostgreSQL version 9.3

Answer 1

No, I am afraid that's not possible. I have been wishing this was possible myself on several occasions. Either you have a registered row (composite) type that matches the return type or you have to list columns individually.

CREATE or REPLACE FUNCTION select_join()
  RETURNS TABLE (col1 int, col2 date, ...) AS ...

Or you create a composite type:

CREATE TYPE foo AS (col1 int, col2 date, ...);

CREATE or REPLACE FUNCTION select_join()
  RETURNS SETOF foo AS ...

A VIEW also registers its row type automatically. For the simple test case you present, you might just use a view instead of the function to begin with (like @a_horse commented):

CREATE OR REPLACE VIEW my_view AS 
SELECT t1.*, t2.name
FROM t1 JOIN t2 ON t1.t2_id = t2.id;

Or you create the view for the purpose of registering the row type conveniently. You might add LIMIT 0 to document that the view is not meant to return rows, but that's optional.

CREATE or REPLACE FUNCTION select_join()
  RETURNS SETOF my_view AS ...

Be aware that this introduces a dependency (like with any other view) and Postgres will prevent dropping the table and some other manipulations, unless you drop depending views first.