By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,558 Members | 1,177 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,558 IT Pros & Developers. It's quick & easy.

Function returning uple?

P: 2
Hello all,

Suppose that there is a table "mytable" with a column "mycolumn", and
two functions f1 and f2. The following select:
Expand|Select|Wrap|Line Numbers
  1. SELECT *, f1(myfield), f2(myfield) from mytable;
  2.  
outputs the columns:
Expand|Select|Wrap|Line Numbers
  1. myfield, f1, f2
  2.  
Is it possible to write a function f that can be invoked like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *, f(myfield) from mytable;
  2.  
and still produce the columns below (add two columns along the columns
from mytable):
Expand|Select|Wrap|Line Numbers
  1. myfield, f1, f2
  2.  
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
Dec 18 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 700
Hello all,

Suppose that there is a table "mytable" with a column "mycolumn", and
two functions f1 and f2. The following select:
Expand|Select|Wrap|Line Numbers
  1. SELECT *, f1(myfield), f2(myfield) from mytable;
  2.  
outputs the columns:
Expand|Select|Wrap|Line Numbers
  1. myfield, f1, f2
  2.  
Is it possible to write a function f that can be invoked like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *, f(myfield) from mytable;
  2.  
and still produce the columns below (add two columns along the columns
from mytable):
Expand|Select|Wrap|Line Numbers
  1. myfield, f1, f2
  2.  
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?
Dec 18 '07 #2

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. f1=projection_point(myfield, point_p): returns a point
  2. f2=projection_distance(myfield, point_p): returns a float
  3.  
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:
Expand|Select|Wrap|Line Numbers
  1. f3=projection_info(myfield, point_p): returns the uple (point, distance)
  2.  
So I would like to replace a call like:
Expand|Select|Wrap|Line Numbers
  1. select *, projection_point(geometry, point_p), projection_distance(geometry,point_p) from mytable;
  2.  
with something like:
Expand|Select|Wrap|Line Numbers
  1. select *, projection_info(geometry, point_p) from mytable;
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. f1 = increase_value(myfield, delta) : returns value(myfield) + delta
  2. f2 = decrease_value(myfield, delta) : returns value(myfield) - delta
  3.  
and the aggregated function would be
Expand|Select|Wrap|Line Numbers
  1. f3=inc_dec(myfield,delta): returns [value(myfield)+delta,value(myfield)-delta]
  2.  
Please let me know if you need any additional information.

Best regards,
Ovidiu
Dec 19 '07 #3

Expert 100+
P: 700
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:
Expand|Select|Wrap|Line Numbers
  1. f1=projection_point(myfield, point_p): returns a point
  2. f2=projection_distance(myfield, point_p): returns a float
  3.  
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:
Expand|Select|Wrap|Line Numbers
  1. f3=projection_info(myfield, point_p): returns the uple (point, distance)
  2.  
So I would like to replace a call like:
Expand|Select|Wrap|Line Numbers
  1. select *, projection_point(geometry, point_p), projection_distance(geometry,point_p) from mytable;
  2.  
with something like:
Expand|Select|Wrap|Line Numbers
  1. select *, projection_info(geometry, point_p) from mytable;
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. f1 = increase_value(myfield, delta) : returns value(myfield) + delta
  2. f2 = decrease_value(myfield, delta) : returns value(myfield) - delta
  3.  
and the aggregated function would be
Expand|Select|Wrap|Line Numbers
  1. f3=inc_dec(myfield,delta): returns [value(myfield)+delta,value(myfield)-delta]
  2.  
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.
Dec 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.