Re: inlining SQL functions

On Apr 25, 2010, at 2:13 PM, Tom Lane wrote:
> Jim Nasby <decibel(at)decibel(dot)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 allow it in the SELECT clause with the same restrictions we place on a subquery in the select clause (namely that you get an 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('Checks Disabled')), 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(at)workbook(dot)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(at)workbook(dot)local=# explain analyze SELECT * FROM test.setting( 'Checks disabled' );
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Function Scan on 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(at)workbook(dot)local=# explain analyze SELECT * FROM test.setting( 'Checks disabled' );
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Function Scan on 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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net