如何在SQL函数中返回自定义类型且无需预先声明该类型？

Absolutely! You don’t have to create a custom type like my_type to return multiple fields from a PostgreSQL function. There are a couple of straightforward alternatives that let you achieve the same result with less upfront setup. Let’s break down the most useful approaches:

1. Return a TABLE type (most intuitive for direct queries) #

This method lets you define the output structure directly in the function’s return clause, making it easy to call without extra syntax. Here’s how to rewrite your example:

CREATE OR REPLACE FUNCTION get() 
RETURNS TABLE(a text, b text, c text) AS $$
BEGIN
    RETURN QUERY
        SELECT r[1]::text, r[2]::text, r[3]::text
        FROM regexp_split_to_array('a.b.c', '\.') r;
END $$ LANGUAGE plpgsql;

To call it, you just run:

SELECT * FROM get();

This returns a proper table with columns a, b, and c—no extra type declarations needed upfront.

2. Use an anonymous record type #

If you prefer a more flexible approach (though it requires a bit more work when calling), you can return an anonymous record:

CREATE OR REPLACE FUNCTION get() 
RETURNS record AS $$
DECLARE
    result_record record;
BEGIN
    SELECT r[1], r[2], r[3] INTO result_record
    FROM regexp_split_to_array('a.b.c', '\.') r;
    RETURN result_record;
END $$ LANGUAGE plpgsql;

Since PostgreSQL doesn’t know the structure of the anonymous record upfront, you need to define it when calling the function:

SELECT * FROM get() AS t(a text, b text, c text);

3. Return a row directly with ROW() constructor #

You can also skip declaring a variable entirely and return a constructed row directly:

CREATE OR REPLACE FUNCTION get() 
RETURNS record AS $$
BEGIN
    RETURN (SELECT ROW(r[1], r[2], r[3]) FROM regexp_split_to_array('a.b.c', '\.') r);
END $$ LANGUAGE plpgsql;

Just like the anonymous record method, you’ll need to specify the column types when calling it using the same syntax as above.

Between these options, the TABLE approach is usually the best choice for most use cases—it’s clear, easy to use, and behaves like a standard query result.

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