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.
