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.
I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself.
Consider this simplified form:
CREATE FUNCTION store_distance(_lat double precision
,_long double precision
,_radius double precision
,_tries integer)
RETURNS TABLE(
store_id store.id%type
,store_name store.name%type
,distance double precision) AS
$func$
DECLARE
_ct int := 0;
_pos point := point(_lat, _long);
BEGIN
LOOP
EXIT WHEN _ct >= _tries
OR EXISTS (
SELECT 1 FROM store s
WHERE point(s.latitude, s.longitude) <@ circle(_pos, _radius));
_radius := _radius * 2;
_ct := _ct + 1;
END LOOP;
RETURN QUERY
SELECT s.id, s.name
,get_distance(_lat, _long, s.latitude, s.longitude)
FROM store s
WHERE point(s.latitude, s.longitude) <@ circle(_pos, _radius);
ORDER BY 3;
END
$func$ LANGUAGE plpgsql STRICT;
I made the function STRICT
to disallow NULL input, which could result in an endless loop.
Note how I use circles with the "Contained" operator <@
instead of a boxes. One would assume calculations to be slightly more expensive than with boxes, but it hardly matters at all once you support your query with a GiST index like:
CREATE INDEX store_point_gist_idx ON store
USING gist (point(latitude, longitude));
You might consider to store lat / lon as point
to begin with and replace the index on an expression with a simpler one on the column. Works either way, just make sure the query matches the index so it gets used. Big difference for big tables.
You may be interested in this closely related answer on SO I posted last year - with a lot more explanation and links.
Best Answer
No it's not possible in functions, independently of their language. The structure of a function result set is always static, just like the result set of any given query. In fact it's related, as this structure (column names and types) must be incorporated into the SQL query that calls the function.
Remember that a client application must be able to prepare a query and obtain a description of its results before executing it. Also a prepared query can be repeateadly executed with different parameters, and cannot return a different structure across executions. It would be impossible to guarantee that property if a function could independantly and dynamically decide what structure it wants to return.
I'm not sure whether the documentation makes that point explicitly somewhere (of course it tends to describe what can be done, not what is impossible) but here are some bits in the protocol flow documentation that support this answer:
In future versions (PostgreSQL 11 or beyond), we might have stored procedures, which are not called through a SQL query but through a special
CALL
statement that is not tied to a specific structure of result set. In fact, a stored procedure should be able to return several result sets with different structures, without prior declaration of the return types. This is currently being worked on and discussed in the -hackers mailing list (see Dynamic result sets from procedures and related recent threads on SQL procedures).But as of PostgreSQL 10, there is no way to practically get dynamic result sets outside of using encapsulating types like JSON or XML.