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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "Mark Dexter"
Дата:
Сообщение: Complex Update Queries with Fromlist
Следующее
От: Michael Fuhr
Дата:
Сообщение: Re: Is there an opposite to pg_get_userbyid() ?