Forum .LRN Q&A: More fixes for Postgres port
Change dotlrn_community__new, in dotlrn/sql/postgresql/communities-package-create.sql, from
c_id integer; BEGIN c_id := acs_group__new (to
c_id integer; real_archived_p char(1); BEGIN if p_archived_p is null then real_archived_p := ''f''; else real_archived_p := p_archived_p; end if; c_id := acs_group__new (And then use real_archived_p in the insert into dotlrn_communities_all.
The problem here is that in Oracle you can put a default value on a function parameter, so it set p_archived_p to 'f' automagically. PG doesn't have that feature so we have to do it ourselves.
2. Could not move portlets around on customize page.
Move portal::swap_element.get_prev_sort_key and portal::swap_element.get_next_sort_key from new-portal/tcl/portal-procs.xql to new-portal/tcl/portal-procs-oracle.xql and new-portal/tcl/portal-procs-postgresql.xql. Modify the Postgres version from
select sort_key as other_sort_key, element_id as other_element_id from (select pem.sort_key, element_id from portal_element_map pem, portal_pages pp where pp.portal_id = :portal_id and pem.page_id = :my_page_id and pp.page_id = pem.page_id and region = :region and pem.sort_key < :my_sort_key and state != 'pinned' order by pem.sort_key desc) where rownum = 1to
select sort_key as other_sort_key, element_id as other_element_id from (select pem.sort_key, element_id from portal_element_map pem, portal_pages pp where pp.portal_id = :portal_id and pem.page_id = :my_page_id and pp.page_id = pem.page_id and region = :region and pem.sort_key < :my_sort_key and state != 'pinned' order by pem.sort_key desc) this limit 1
(this is get_prev_sort_key; get_next_sort_key is similar)
Also create portal::swap_element.get_portal_element_map_nextval for both databases:
Oracle: select portal_element_map_sk_seq.nextval from dual
Postgres: select nextval('portal_element_map_sk_seq')
And use this query in portal-procs.tcl, function swap_element, instead of db_nextval.
The first changes are due to Postgres syntax differences. The second is because the way nextval has been simulated isn't reliable in PG 7.2, so it is not being used consistently anymore and this is one place where the old way wasn't working.
3. Could not delete pages from customized portal.
In new-portal/sql/postgresql/api-create.sql, change
create function portal_page__delete(integer) returns integer as ' declare p_page_id integer;to
create function portal_page__delete(integer) returns integer as ' declare p_page_id alias for $1;
The function doesn't do much without the passed-in page_id! :)
That's all for tonight...
Thanks for pointing us at this bug. The fix of checking p_archive_p may appear to work, but it will probably cause errors down the road. The real problem here was that the call to define_function_args for dotlrn_community__new had some parameters out of order, so when package_instantiate_object is called for a dotlrn_community, a different parameter was being passed as p_archive_p than what would have been expected.
define_function_args also has the facility for defining default values for use by package_instantiate_object. You can specify these by adding a ";default_value" in the parameter list after the parameter to receive a default value. The updated define_function_args for dotlrn_community__new, with a default of 'f' for p_archive_p is:
You can copy this line into plsql and it will override the old define_function_args.
For now, I've proposed a hack to db_nextval that will make the query work for both sequences that are sequences, as well as sequences that are views-acting-as-sequences. That thread is here.
With this updated db_nextval, moving the portlets around works without splitting up the queries, and will also alleviate the need to split up future nextval queries into multiple xql files. Let's see what people have to say about this approach in that thread.
re the p_page_id bug, Thank you! It's now fixed in the cvs tree.
INSERT INTO TRAVEL(LOGIN_ID, LOCK_SESSION_ID, LOCK_OPERATION, LOCK_KEY1 ,USER_NAME ) SELECT 'RAM','CD7F','TER','21','null' from DUAL where not exists ( SELECT * FROM TRAVEL A WHERE A.LOCK_OPERATION = 'TER' AND LOCK_KEY1 = '21');
can u help me to ececute same query in postgresql