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 + 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 SELECT *, f1(myfield), f2(myfield) from mytable;   outputs the columns: Expand|Select|Wrap|Line Numbers myfield, f1, f2   Is it possible to write a function f that can be invoked like this: Expand|Select|Wrap|Line Numbers SELECT *, f(myfield) from mytable;   and still produce the columns below (add two columns along the columns from mytable): Expand|Select|Wrap|Line Numbers 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 Dec 18 '07 #1
Share this Question
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 SELECT *, f1(myfield), f2(myfield) from mytable;   outputs the columns: Expand|Select|Wrap|Line Numbers myfield, f1, f2   Is it possible to write a function f that can be invoked like this: Expand|Select|Wrap|Line Numbers SELECT *, f(myfield) from mytable;   and still produce the columns below (add two columns along the columns from mytable): Expand|Select|Wrap|Line Numbers 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? 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 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: Expand|Select|Wrap|Line Numbers f3=projection_info(myfield, point_p): returns the uple (point, distance)   So I would like to replace a call like: Expand|Select|Wrap|Line Numbers select *, projection_point(geometry, point_p), projection_distance(geometry,point_p) from mytable;   with something like: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers f1 = increase_value(myfield, delta) : returns value(myfield) + delta f2 = decrease_value(myfield, delta) : returns value(myfield) - delta   and the aggregated function would be Expand|Select|Wrap|Line Numbers 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 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 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: Expand|Select|Wrap|Line Numbers f3=projection_info(myfield, point_p): returns the uple (point, distance)   So I would like to replace a call like: Expand|Select|Wrap|Line Numbers select *, projection_point(geometry, point_p), projection_distance(geometry,point_p) from mytable;   with something like: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers f1 = increase_value(myfield, delta) : returns value(myfield) + delta f2 = decrease_value(myfield, delta) : returns value(myfield) - delta   and the aggregated function would be Expand|Select|Wrap|Line Numbers 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. Dec 19 '07 #4

### Post your reply

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