PostgreSQL stored procedure which can return one of two custom rowtypes I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this:

CREATE OR REPLACE FUNCTION get_numbers(_type integer)
    RETURNS SETOF RECORD
    LANGUAGE plpgsql
AS $$
    DECLARE
        _number_column text;
    BEGIN
        CASE _type
            WHEN 1 THEN
                _column_name := 'normal_numbers';
            WHEN 2 THEN
                _column_name := 'special_numbers';
        END CASE;

        RETURN QUERY EXECUTE 'SELECT * FROM ' || quote_ident(_column_name)
    END;
END;


And I have two different custom composite types:

CREATE TYPE normal_number AS (
    id_normal_number integer,
    normal_number text,
);

CREATE TYPE special_number AS (
    id_special_number integer,
    special_number text,
);


When I call it SELECT * FROM get_numbers(1) I want to get:

id_normal_number  normal_number
-------------------------------
1                 00001
2                 00002
3                 00003


And for SELECT * FROM get_numbers(2) it should return:

id_special_number  special_number
--------------------------------
1                  S0001
2                  S0002
3                  S0003


The point is to call one function, execute dynamic SQL query, get some columns and cast it to custom row type according to procedure parameter (to have different column names in the result).

I tried to create another 2 procedures get_normal_numbers() and get_special_numbers() declared with RETURNS SETOF normal_number / RETURNS SETOF special_number and from the inside call get_numbers(1) or get_number(2) but I didn't manage to get it working.

I don't know how to properly cast the returned set of rows or how to correctly return that set from get_numbers(1) -&gt; get_normal_numbers() -&gt; application

I can edit / add some more details if it'll be needed.
Since your two types are composed from the same basic types in the same order, they are compatible with each other.  So the return type will be the same for functions using them - if I understand correctly, your problem is you want to have the different names of the returned columns depending on the type.

This cannot be done from the database, but you can easily do it from your application.  It can be done like (ugly pseudocode)

if type == 1 {
    execute("SELECT 1 AS id_normal_number, '00001' AS normal_number FROM your_function()")
} else {
    execute("SELECT 1 AS id_special_number, 'S0001' AS special_number FROM your_function()")
}


ie. changing only the names, but using the same DB function.  Or you can invoke two different functions, this way you can avoid dynamic SQL, which may be desirable for readability/performance, depending on some circumstances.