Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL wants column types for a function that returns record

ErrorERROR: 42601: a column definition list is required for functions returning "record"

Short answer

A function declared to return the pseudo-type record does not say what columns it gives back. PostgreSQL needs that in the query so it can parse and plan it. Add a column definition list after the call, such as AS t(a int, b text), or declare the function with OUT parameters or RETURNS TABLE.

  • Applies to PostgreSQL 16
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Look up how the function is declared. A return type of record with no OUT parameters is the trigger.

    \df+ my_function
  2. 2Match what you see, then jump to its fix

    1. 1SELECT * FROM my_function(...) fails right awayThe function returns record and the query gives no column listFix
    2. 2The call is dblink(...) or json_to_recordset(...)A remote or generic function such as dblink or json_to_recordsetFix
    3. 3Every caller must repeat the same column listYou own the function and can declare its columns onceFix

What does the error look like?

  • ERROR: 42601: a column definition list is required for functions returning "record"

Cause 1 of 3

The function returns record and the query gives no column list

1Check

A table function can be declared as returning record with no OUT parameters. Then the expected row structure must be written in the query.

\df+ my_function

2Fix

Add a column definition list that names every column and its type.

SELECT *
FROM my_function(42) AS t(id integer, label text);

3Verify

The query returns the columns you named.

SELECT id, label FROM my_function(42) AS t(id integer, label text) LIMIT 1;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

A remote or generic function such as dblink or json_to_recordset

1Check

These functions can return any shape, so the caller always supplies the columns. With ROWS FROM the list can be attached to each function.

\df+ json_to_recordset

2Fix

Write the types inline. With ROWS FROM(), attach the list to that function call.

SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
  AS t1(proname name, prosrc text);

SELECT *
FROM ROWS FROM (json_to_recordset('[{"a":40,"b":"foo"}]') AS (a integer, b text));

3Verify

Each query returns rows with the named columns.

SELECT * FROM ROWS FROM (json_to_recordset('[{"a":1,"b":"x"}]') AS (a integer, b text));

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

You own the function and can declare its columns once

1Check

The record-with-no-OUT form is meant for functions whose shape varies. When the shape is fixed, OUT parameters or RETURNS TABLE state the columns in the function itself.

\sf my_function

2Fix

Recreate the function with RETURNS TABLE, then call it without a list.

CREATE FUNCTION my_function(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

3Verify

A plain SELECT * works.

SELECT * FROM my_function(42);

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Prefer RETURNS TABLE or OUT parameters for fixed shapes so callers need no list.
  • Keep the column list in one view when a record function is unavoidable, so callers do not repeat it.
  • Write the list in the same order as the function's result.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.