ParAccel --> Script Generator and function node
1. I have schema folder grouping = yes
For the schema pg_catalog, the Procedures and Functions tree node displays no objects
For System Functions, there are a few functions
2. Now, I use Tools -- > Schema script generator and script the functions from the schema pg_catalog
The list displays around 1676 functions which are not displayed in the schema browser
see pic
|
53 KB
Schema tree node did not call ExtractFunction to get list of defined functions but create its own SELECT statement to perform functions look up; this custom select statement, shown below, explicitly excludes those functions defined in 'pg_catalog' and 'information_schema':
SELECT pn.nspname,p.proname,p.prorettype,p.proargtypes, t.typtype,t.typrelid, t.typname, NULL as proallargtypes, p.oid
FROM pg_catalog.pg_proc p, pg_catalog.pg_language pl,pg_catalog.pg_type t, pg_catalog.pg_namespace pn
WHERE p.prolang = pl.oid and p.prorettype=t.oid and p.pronamespace = pn.oid
and pn.nspname != 'pg_catalog' and pn.nspname != 'information_schema' and (pl.lanname != 'internal' and pl.lanname != 'c')
and t.typname != 'void' and pn.nspname = 'pg_catalog' ORDER BY pn.nspname, p.proname
I am assuming we did this on purpose (same behavior as PostgreSQL) and reassigned this issue to Niels for validation.
On the other hand, Schema Script Generator uses ExtractFunction API to get list of defined functions, the generated SELECT statement is:
select pn.nspname, proname, prosrc, lanname, proargtypes, pp.proname || '_' || pp.oid AS specific_name, proargnames
from pg_catalog.pg_proc pp, pg_catalog.pg_language pl, pg_namespace pn, pg_catalog.pg_type t
where pp.prolang = pl.oid and pp.pronamespace = pn.oid and pp.prorettype=t.oid and pl.lanname != 'c'
and pn.nspname = 'pg_catalog' and t.typname != 'void' order by proname
If you look at the tree nodes, there should be two nodes for functions, (1) functions (2) system functions. The system functions contains all functions in any pg_* schema & information schema. The functions node contains all other functions as they would be user defined.
In the schema script generator we do not seperate functions as user defined and system defined, so they will all appear. The user can deselect functions from those two schemas if they wanted to...
This is as designed...
If you look at the tree nodes, there should be two nodes for functions, (1) functions (2) system functions. The system functions contains all functions in any pg_* schema & information schema. The functions node contains all other functions as they would be user defined.
In the schema script generator we do not seperate functions as user defined and system defined, so they will all appear. The user can deselect functions from those two schemas if they wanted to...
This is as designed...
Issue #6287 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
No fixed build |
No time estimate |
Schema tree node did not call ExtractFunction to get list of defined functions but create its own SELECT statement to perform functions look up; this custom select statement, shown below, explicitly excludes those functions defined in 'pg_catalog' and 'information_schema':
SELECT pn.nspname,p.proname,p.prorettype,p.proargtypes, t.typtype,t.typrelid, t.typname, NULL as proallargtypes, p.oid
FROM pg_catalog.pg_proc p, pg_catalog.pg_language pl,pg_catalog.pg_type t, pg_catalog.pg_namespace pn
WHERE p.prolang = pl.oid and p.prorettype=t.oid and p.pronamespace = pn.oid
and pn.nspname != 'pg_catalog' and pn.nspname != 'information_schema' and (pl.lanname != 'internal' and pl.lanname != 'c')
and t.typname != 'void' and pn.nspname = 'pg_catalog' ORDER BY pn.nspname, p.proname
I am assuming we did this on purpose (same behavior as PostgreSQL) and reassigned this issue to Niels for validation.
On the other hand, Schema Script Generator uses ExtractFunction API to get list of defined functions, the generated SELECT statement is:
select pn.nspname, proname, prosrc, lanname, proargtypes, pp.proname || '_' || pp.oid AS specific_name, proargnames
from pg_catalog.pg_proc pp, pg_catalog.pg_language pl, pg_namespace pn, pg_catalog.pg_type t
where pp.prolang = pl.oid and pp.pronamespace = pn.oid and pp.prorettype=t.oid and pl.lanname != 'c'
and pn.nspname = 'pg_catalog' and t.typname != 'void' order by proname