PostgreSQL自定义类型返回查询结构不匹配问题求解

The error you're hitting happens because PostgreSQL can't automatically map the anonymous row you're constructing (for the sbjct field) and your count value to your custom sbjct_with_count composite type. Even though the field list looks right, the database doesn't recognize the anonymous row as an explicit sbjct_v1 type, so it can't align the whole result to your target type.

Here are a couple of straightforward fixes:

Option 1: Explicitly cast the row to sbjct_v1 #

Since your sbjct_with_count type expects a sbjct_v1 as its first field, you need to tell PostgreSQL that the row of fields from dp.sbjct is exactly that type. You can do this with a cast (::):

RETURN QUERY 
SELECT 
  -- Cast the constructed row to dp.sbjct_v1 explicitly
  ROW(s.sbjct_vrsn_id, s.sbjct_id, s.nm, s.dsc, s.db, s.tbl, s.pk, s.crtd_by, s.crtd_ts, s.aprvd_by, s.aprvd_ts, s.aprvd_stat, s.aprvd_del)::dp.sbjct_v1 AS sbjct,
  (SELECT COUNT(*) ... subquery) AS num_metrics
FROM dp.sbjct s ... rest of query;

Option 2: Shortcut if the table matches the type exactly #

If the dp.sbjct table has the exact same field order and data types as your sbjct_v1 type (which it looks like it does from your definition), you can simplify this even more by casting the entire row directly:

RETURN QUERY 
SELECT 
  s::dp.sbjct_v1 AS sbjct,
  (SELECT COUNT(*) ... subquery) AS num_metrics
FROM dp.sbjct s ... rest of query;

Option 3: Directly construct the sbjct_with_count row #

You can also skip the separate field aliases and build the target composite type directly:

RETURN QUERY 
SELECT 
  ROW(
    -- Cast inner row to sbjct_v1 first
    ROW(s.sbjct_vrsn_id, s.sbjct_id, s.nm, s.dsc, s.db, s.tbl, s.pk, s.crtd_by, s.crtd_ts, s.aprvd_by, s.aprvd_ts, s.aprvd_stat, s.aprvd_del)::dp.sbjct_v1,
    (SELECT COUNT(*) ... subquery)
  )::dp.sbjct_with_count
FROM dp.sbjct s ... rest of query;

Any of these approaches will make PostgreSQL recognize that your query's output structure exactly matches the sbjct_with_count type, resolving the error.

内容的提问来源于stack exchange，提问作者KBouldin9