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 param…

Accepted Answer

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.

Declaring the tuple structure of a record in PL/pgSQL

Solution

Problem

Related problems