[GENERAL] 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