Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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) -> get_normal_numbers() -> application

I can edit / add some more details if it'll be needed.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

PostgreSQL queries must return a single definite type when they are run, and that type has to be known at planning time (i.e. before the function is run). This significantly limits your options, but you basically have two options:

  1. You can standardize the return type of the function. I prefer this because it means that the contract is tighter regarding the function and the software calling it knows better what to expect.

  2. You can return a refcursor, which the application then runs a FETCH on client-side. This means you can essentially return a reference to a row set of an arbitrary type. I don't like this as much because it interferes with having a useful contract between the application and the database. It is however useful when returning multiple record sets since you can return a SETOF REFCURSOR...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.