Re: record from plpgsql function performance

[Alexander Shereshevsky <shereshevsky@xxxxxxxxx>]

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.johnston@xxxxxxxxx> wrote:

On Thursday, July 2, 2015, Alexander Shereshevsky <shereshevsky@xxxxxxxxx> 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.