如何从PL/pgSQL函数返回行构造器？遇类型不匹配错误求解决

云原生阿岚

2026-5-29

Why PL/pgSQL Function Fails with Row Constructor (But SQL Version Works)

Let's break down why your PL/pgSQL function throws an error while the SQL equivalent runs smoothly, plus how to fix it.

The Root of the Issue

Your two functions look nearly identical, but PostgreSQL handles their return logic differently:

The SQL function test_sql() works because PostgreSQL automatically unpacks the row constructor (1, 1) into two separate integer columns—exactly matching your RETURNS TABLE (a int, b int) definition.
The PL/pgSQL function test_plpgsql() fails because RETURN QUERY SELECT (1, 1) treats the row constructor as a single column of type record, not two distinct integers. This clashes with the return table's expected structure, which is why you get the error: "Returned type record does not match expected type integer in column 1."

Fixes for the PL/pgSQL Function

You have two straightforward ways to resolve this:

1. Return Separate Columns (Simplest Fix)

Drop the parentheses to select two distinct values instead of a single row constructor:

CREATE FUNCTION test_plpgsql() RETURNS TABLE ( a int, b int ) LANGUAGE plpgsql IMMUTABLE AS $$ 
BEGIN 
  RETURN QUERY SELECT 1, 1; 
END; 
$$;

2. Explicitly Unpack the Row Constructor

If you need to use a row constructor (e.g., for more complex row values), use the .* operator to expand the row into individual columns:

CREATE FUNCTION test_plpgsql() RETURNS TABLE ( a int, b int ) LANGUAGE plpgsql IMMUTABLE AS $$ 
BEGIN 
  RETURN QUERY SELECT (ROW(1, 1)).*; 
END; 
$$;

Why SQL and PL/pgSQL Behave Differently

SQL functions interpret SELECT (val1, val2) as shorthand for SELECT val1, val2 when the return type is a table with a matching column count. PL/pgSQL's RETURN QUERY is more literal: it takes the exact result set of the SELECT statement, so a row constructor stays a single record column unless you explicitly unpack it.