Function returning uple?

[oghogh]

Hello all,

Suppose that there is a table "mytable" with a column "mycolumn", and
two functions f1 and f2. The following select:

SELECT *, f1(myfield), f2(myfield) from mytable;
 

outputs the columns:

myfield, f1, f2
 

Is it possible to write a function f that can be invoked like this:

SELECT *, f(myfield) from mytable;
 

and still produce the columns below (add two columns along the columns
from mytable):

myfield, f1, f2
 

The reason for this question is that f1 and f2 are expensive to compute separately, but it would be more efficient to be both computed in a single function.

Thanks in advance,
Ovidiu

[rski]

Hello all,

Suppose that there is a table "mytable" with a column "mycolumn", and
two functions f1 and f2. The following select:

SELECT *, f1(myfield), f2(myfield) from mytable;
 

outputs the columns:

myfield, f1, f2
 

Is it possible to write a function f that can be invoked like this:

SELECT *, f(myfield) from mytable;
 

and still produce the columns below (add two columns along the columns
from mytable):

myfield, f1, f2
 

The reason for this question is that f1 and f2 are expensive to compute separately, but it would be more efficient to be both computed in a single function.

Thanks in advance,
Ovidiu

I do not understand what do you want. You say you have table with column mycolumn and use select
select *,f1(myfield),f2(myfield) from mytable; ??
What is myfield? What do the functions f1, f2 do?

[oghogh]

Hello,

Thanks for pointing this out, there was an error in my post, "myfield" is actually the field of the table previously referred as "mycolumn".

Suppose that "myfield" is a polyline (in postGIS) and the two functions are:

f1=projection_point(myfield, point_p): returns a point
f2=projection_distance(myfield, point_p): returns a float
 

where point_p has a value I specify.

Computing f1 and f2 separately is expensive (one requires the other to be known), but they could be both computed more efficiently in a single call to:

f3=projection_info(myfield, point_p): returns the uple (point, distance)
 

So I would like to replace a call like:

select *, projection_point(geometry, point_p), projection_distance(geometry,point_p) from mytable;
 

with something like:

select *, projection_info(geometry, point_p) from mytable;
 

where "geometry" is a column of "mytable" and "point_p" a given point.

The rows that result from executing the last SQL should contain all the fields from "mytable", to which two fields are added: one containing the projection point, and the other the projection distance.

I would like to know if this is possible at all in PostgreSQL - for testing purposes we could work with:

f1 = increase_value(myfield, delta) : returns value(myfield) + delta
f2 = decrease_value(myfield, delta) : returns value(myfield) - delta
 

and the aggregated function would be

f3=inc_dec(myfield,delta): returns [value(myfield)+delta,value(myfield)-delta]
 

Please let me know if you need any additional information.

Best regards,
Ovidiu

[rski]

Hello,

Thanks for pointing this out, there was an error in my post, "myfield" is actually the field of the table previously referred as "mycolumn".

Suppose that "myfield" is a polyline (in postGIS) and the two functions are:

f1=projection_point(myfield, point_p): returns a point
f2=projection_distance(myfield, point_p): returns a float
 

where point_p has a value I specify.

Computing f1 and f2 separately is expensive (one requires the other to be known), but they could be both computed more efficiently in a single call to:

f3=projection_info(myfield, point_p): returns the uple (point, distance)
 

So I would like to replace a call like:

select *, projection_point(geometry, point_p), projection_distance(geometry,point_p) from mytable;
 

with something like:

select *, projection_info(geometry, point_p) from mytable;
 

where "geometry" is a column of "mytable" and "point_p" a given point.

The rows that result from executing the last SQL should contain all the fields from "mytable", to which two fields are added: one containing the projection point, and the other the projection distance.

I would like to know if this is possible at all in PostgreSQL - for testing purposes we could work with:

f1 = increase_value(myfield, delta) : returns value(myfield) + delta
f2 = decrease_value(myfield, delta) : returns value(myfield) - delta
 

and the aggregated function would be

f3=inc_dec(myfield,delta): returns [value(myfield)+delta,value(myfield)-delta]
 

Please let me know if you need any additional information.

Best regards,
Ovidiu

Well my english is really poor but if you're asking if postgres function can return a record (or multiple values) the answer is yes. Just see in documentatioon.