PostgreSQL stored procedure which can return one of two custom rowtypes

postgresqlstored-procedures

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.

Best Answer

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.

Related Solutions

PostgreSQL – Concatenating SETOF Type or SETOF Record

The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$                          
language sql;

or better, get rid of it entirely and write the whole operation like this:

-- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

Resulting in:

regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series 
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

This exploits PostgreSQL's behaviour with set-returning functions called in the SELECT list. Once PostgreSQL 9.3 comes out it can be replaced with a standards-compliant LATERAL query.

Since it turns out your question was a simplified version of the real problem, let's tackle that. I'll work with the simplified compute_pair_id_value above to avoid the hassle of plpython3. Here's how to do what you want:

SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

Result:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

but again, be warned that compute_pair_id_value will be called more than once. This is a limitation of PostgreSQL's query executor that can be avoided in 9.3 with LATERAL support, but as far as I know you're stuck with it in 9.2 and below. Observe:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$             
language plpgsql;

output:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

See how compute_pair_id_value is called once per output column?

There is a workaround: Another layer of subquery to unpack the composite type result. See:

regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

You can use the same technique in your code if you really must LOOP over the results (it's slow to do that, so avoid it if you can).

Postgres – Fix Ranges Over Domains Not Triggering Implicit Casting

I think this is a bug and you should report it. I think it can be demonstrated a lot easier.

SELECT '[1,5]'::int4range @> 3;

CREATE DOMAIN zdomain AS int;
CREATE TYPE myrange_int     AS RANGE ( subtype = int );
CREATE TYPE myrange_zdomain AS RANGE ( subtype = zdomain );

-- WORKS
SELECT '[1,5]'::myrange_int     @> 3;
SELECT '[1,5]'::myrange_zdomain @> 3::zdomain;

-- DOES NOT WORK
SELECT '[1,5]'::myrange_zdomain @> 3;
ERROR:  operator does not exist: myrange_zdomain @> integer
LINE 1: SELECT '[1,5]'::myrange_zdomain @> 3;
                                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I think what you want is totally reasonable. I don't think it's going to be a high priority item. My assumption is that coercion that is triggered is not looking to see if the range is over a domain, and it should.

Feel free to update your question with my example, I think it'll be a lot easier for people to follow

Best Answer

Related Solutions

PostgreSQL – Concatenating SETOF Type or SETOF Record

Postgres – Fix Ranges Over Domains Not Triggering Implicit Casting

Related Question