7.4 in-lining of SQL functions
От | Mike Mascari |
---|---|
Тема | 7.4 in-lining of SQL functions |
Дата | |
Msg-id | 416F179B.5070604@mascari.com обсуждение исходный текст |
Ответы |
Re: 7.4 in-lining of SQL functions
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: