On Apr 25, 2010, at 2:13 PM, Tom Lane wrote:
> Jim Nasby <[email protected]> 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.
[email protected]=# 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
[email protected]=# 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)
[email protected]=# 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 [email protected]
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
