Re: inlining SQL functions - Mailing list pgsql-hackers

On Apr 25, 2010, at 2:13 PM, Tom Lane wrote:
> Jim Nasby <decibel@decibel.org> writes:
>> On Apr 2, 2010, at 12:12 PM, Tom Lane wrote:
>>> If you're thinking of just replacing the call with a sub-SELECT
>>> construct, that's no good in general because it would change the
>>> semantics.
>
>> Since Alexey was working on this for us, I'll elaborate. The actual
>> use case is below.
>
> There are two reasons why that function doesn't get inlined: it's not
> declared to return SETOF, and it's (implicitly) declared VOLATILE.
> If you make it SETOF and STABLE, it'll get inlined.  Both of those
> things change the semantics, so it's hard to do inlining otherwise.

That didn't seem to help, but possibly because I'm on 8.3 (see below). However, I've got some other questions...

A lot of what I'd like to use this for is simplifying lookups, ie:

plpgsql: v_disabled := test.setting_b( 'Checks disabled' );
or
SELECT ..., test.setting_b( 'Checks disabled' )

Where setting_b is FUNCTION(text) RETURNS boolean AS SELECT b FROM test.setting($1)

I would hope that would eventually get turned into...

SELECT ..., ( SELECT b FROM tools.settings WHERE ... )

I understand that's not the case now, but does it have to be that way? Perhaps if a function is defined SETOF we can
allowit in the SELECT clause with the same restrictions we place on a subquery in the select clause (namely that you
getan error if it returns multiple records). 

I know this could be viewed as simply being syntactic sugar, but consider the options from my actual code:

IF tools.checks__disabled() OR p_condition THEN

or...

IF (current_database() NOT LIKE '%_prod' AND coalesce((SELECT b FROM tools.settings WHERE lower(setting_name) =
lower('ChecksDisabled')), false) OR p_condition THEN 

And tools.checks__disabled() is used in more than one place, so this is even more important than syntactic sugar.



decibel@workbook.local=# create or replace FUNCTION test.setting(text) RETURNS SETOF test.settings  STABLE LANGUAGE sql
 AS $_$ 
SELECT * FROM test.settings WHERE lower(setting_name) = lower($1)
$_$;
CREATE FUNCTION
decibel@workbook.local=# explain analyze  SELECT * FROM test.setting( 'Checks disabled' );
                 QUERY PLAN                                                  
------------------------------------------------------------------------------------------------------------Function
Scanon setting  (cost=0.00..260.00 rows=1000 width=77) (actual time=0.160..0.160 rows=1 loops=1)Total runtime: 0.176 ms 
(2 rows)

decibel@workbook.local=# explain analyze  SELECT * FROM test.setting( 'Checks disabled' );
                 QUERY PLAN                                                  
------------------------------------------------------------------------------------------------------------Function
Scanon setting  (cost=0.00..260.00 rows=1000 width=77) (actual time=0.130..0.130 rows=1 loops=1)Total runtime: 0.143 ms 
(2 rows)

--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net