Declaring the tuple structure of a record in PL/pgSQL

Question

I can't find anything in the PostgreSQL documentation that shows how to declare a record, or row, while declaring the tuple structure at the same time. If you don't define you tuple structure you get the error "The tuple structure of a not-yet-assigned record is indeterminate".

This is what I'm doing now, which works fine, but there must be a better way to do it.

CREATE OR REPLACE FUNCTION my_func()
  RETURNS TABLE (
    "a" integer,
    "b" varchar
  ) AS $$
DECLARE r record;
BEGIN

CREATE TEMP TABLE tmp_t (
    "a" integer,
    "b" varchar
);
-- Define the tuple structure of r by SELECTing an empty row into it.
-- Is there a more straight-forward way of doing this?
SELECT * INTO r
FROM tmp_t;

-- Now I can assign values to the record.
r.a := at.something FROM "another_table" at
       WHERE at.some_id = 1;

-- A related question is - how do I return the single record 'r' from
-- this function?
-- This works:
RETURN QUERY
SELECT * FROM tmp_t;

-- But this doesn't:
RETURN r;
-- ERROR:  RETURN cannot have a parameter in function returning set

END; $$ LANGUAGE plpgsql;

Answer 1

You are mixing the syntax for returning SETOF values with syntax for returning a single row or value.

-- A related question is - how do I return the single record 'r' from

When you declare a function with RETURNS TABLE, you have to use RETURN NEXT in the body to return a row (or scalar value). And if you want to use a record variable with that it has to match the return type. Refer to the code examples further down.

Return a single value or row

If you just want to return a single row, there is no need for a record of undefined type. @Kevin already demonstrated two ways. I'll add a simplified version with OUT parameters:

CREATE OR REPLACE FUNCTION my_func(OUT a integer, OUT b text)
   AS
$func$
BEGIN
   a := ...;
   b := ...;
END
$func$ LANGUAGE plpgsql;

You don't even need to add RETURN; in the function body, the value of the declared OUT parameters will be returned automatically at the end of the function - NULL for any parameter that has not been assigned.
And you don't need to declare RETURNS RECORD because that's already clear from the OUT parameters.

Return a set of rows

If you actually want to return multiple rows (including the possibility for 0 or 1 row), you can define the return type as RETURNS ...

• SETOF some_type, where some_type can be any registered scalar or composite type.

• TABLE (col1 type1, col2 type2) - an ad-hoc row type definition.

• SETOF record plus OUT parameters to define column names andtypes.
  100% equivalent to RETURNS TABLE.

• SETOF record without further definition. But then the returned rows are undefined and you need to include a column definition list with every call (see example).

The manual about the record type:

Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command.

There is more, read the manual.

You can use a record variable without assigning a defined type, you can even return such undefined records:

CREATE OR REPLACE FUNCTION my_func()
  RETURNS SETOF record AS
$func$
DECLARE
    r record;
BEGIN
    r := (1::int, 'foo'::text); RETURN NEXT r; -- works with undefined record
    r := (2::int, 'bar'::text); RETURN NEXT r;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM my_func() AS x(a int, b text);

But this is very unwieldy as you have to provide the column definition list with every call. It can generally be replaced with something more elegant:

• If you know the type at time of function creation, declare it right away (RETURNS TABLE or friends).

CREATE OR REPLACE FUNCTION my_func()
  RETURNS SETOF tbl_or_type AS
$func$
DECLARE
    r tbl_or_type;
BEGIN
    SELECT INTO tbl_or_type  * FROM tbl WHERE id = 10;
    RETURN NEXT r;  -- type matches

    SELECT INTO tbl_or_type  * FROM tbl WHERE id = 12;
    RETURN NEXT r;

    -- Or simpler:
    RETURN QUERY
    SELECT * FROM tbl WHERE id = 14;
END
$func$ LANGUAGE plpgsql;

• If you know the type at time of the function call, there are more elegant ways using polymorphic types:
  Refactor a PL/pgSQL function to return the output of various SELECT queries

Your question is unclear as to what you need exactly.

Answer 2

There might be some way that avoids the explicit type declaration, but offhand the best I can come up with is:

CREATE TYPE my_func_return AS (
    a integer,
    b varchar
  );

CREATE OR REPLACE FUNCTION my_func()
  RETURNS my_func_return AS $$
DECLARE
  r my_func_return;
BEGIN
  SELECT 1, 'one' INTO r.a, r.b;
  RETURN r;
END; $$ LANGUAGE plpgsql;

Oh, I almost forgot the simplest way to do this:

CREATE OR REPLACE FUNCTION my_func2(out a int, out b text)
  RETURNS RECORD AS $$
BEGIN
  SELECT 1, 'one' INTO a, b;
  RETURN;
END; $$ LANGUAGE plpgsql;