Thanks, David. Works perfect. Best Regards, Alexander Shereshevsky +972-52-7460635
On Thu, Jul 2, 2015 at 5:47 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thursday, July 2, 2015, Alexander Shereshevsky <shereshev...@gmail.com> > wrote: > >> Hello, >> >> I have some simple function. The returned data set is generated based on >> view (dynamic - can be changed on daily basis). >> So the function was defined this way: >> >> 1. returns setof some_view as ... >> 2. inside the function I'm generating dynamic SQL into v_sql variable. >> 3. return query execute v_sql >> >> Everything works fine if I'm running single function, like: >> select * from function (param1, param2) >> I'm getting the delimited fields in desired order. >> >> But if I want to run the function in inline mode, like: >> select function(param1, param2) from some_table; >> so the returned datatype is record. >> >> To return multiple fields, I'm trying to use: >> select (function(param1, param2)).* from some_table; >> But this operation has a huge performance impact, IMHO runtime multiplied >> by number of columns - the function is executed for each column separately. >> In my case normal inline run is about 2 seconds for 300-400 records, but >> with ().* it's increased to 90-120 seconds. >> >> Thank you in advance if you can suggest me the better way. >> >> BR, >> Alexander Shereshevsky >> > > Use LATERAL. > > If that is not an option you place the unexpanded function call in a CTE > (with) and the expand it within the main query. > > With funccall as ( select func(arg) from tbl ) > Select (func).* from funccall; > > Because, yes the function is called once for each column due to the star > expansion. You have to keep the result as a composite type during function > execution and then expand the composite type. > > David J. >
