PostgreSQL wants column types for a function that returns record
ERROR: 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.
Fix it fast
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_function2Match what you see, then jump to its fix
- 1
SELECT * FROM my_function(...) fails right awayThe function returns record and the query gives no column listFix - 2
The call is dblink(...) or json_to_recordset(...)A remote or generic function such as dblink or json_to_recordsetFix - 3
Every caller must repeat the same column listYou own the function and can declare its columns onceFix
- 1
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_function2Fix
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
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_recordset2Fix
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
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_function2Fix
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix