7.4 in-lining of SQL functions

[Mike Mascari]

Hello.

I'm writing SQL functions that take an action code and determine the
rows visible by accessing application-maintained privilege tables.
Here's an example:

CREATE FUNCTION sql_areas(bigint) RETURNS SETOF bigint AS '

SELECT _areas.area
FROM _members, _webgroups, _stores, _areas
WHERE _members.webuser = getWebuser() AND
_members.webgroup = _webgroups.webgroup AND
_webgroups.company = _stores.company AND
_stores.store = _areas.store AND
_webgroups.isroot AND
_members.deactive IS NULL AND
_webgroups.deactive IS NULL
UNION
SELECT _areas.area
FROM privileges, privobjs, _areas
WHERE privileges.action = $1 AND
privobjs.relname = ''areas'' AND
privobjs.privobj = privileges.privobj AND
((privileges.isparent = true AND
privileges.objid = _areas.store) OR
(privileges.isparent = false AND
privileges.objid = _areas.area)) AND
(privileges.grantee = getWebuser() OR
privileges.grantee IN (
SELECT _members.webgroup
FROM _members
WHERE _members.webuser = getWebuser() AND
_members.deactive IS NULL
)
)

' LANGUAGE 'sql' STABLE;

I then want to build views atop this function like so:

CREATE VIEW areas AS
SELECT _areas.*
FROM _areas, sql_areas(5) x
WHERE _areas.area = x;

I then have queries like:

SELECT *
FROM areas
WHERE areas.name = 'Foo';

which I suppose would be recursively transformed by the planner into
something far more interesting. But the wording of the 7.4 changelog of

"Simple SQL functions can now be inlined by including their SQL in the
main query. This improves performance by eliminating per-call overhead.
That means simple SQL functions now behave like macros."

has me a bit worried. What does "simple" mean? Will the planner be able
to treat my underlying SQL-language functions as macros and in-line them
into the final query for full optimization possibilities? In fact, my
plan is to have:

SQL-language function
VIEW 1 accessing SQL function
VIEW 2 accessing VIEW 1
SQL query accessing VIEW 2

Should I abandon the SQL-language function, which eliminates some
redundant code elsewhere and incorporate the functions myself into View
1, or can I count on PostgreSQL doing it for me?

Mike Mascari



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Mike Mascari <ma*****@mascari.com> writes:

"Simple SQL functions can now be inlined by including their SQL in the
main query. This improves performance by eliminating per-call overhead.
That means simple SQL functions now behave like macros." has me a bit worried. What does "simple" mean?

Not anything involving UNION :-(

The basic intent of the current inlining facility is to support inlining
of functions that return scalar results. I think that it punts entirely
for functions that return SETOF anything; it certainly shouldn't be
expected to do amazing feats of query optimization for them...

You would probably have better results using views, if you can express
your problems using views. Those do get "inlined" pretty well.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly