Forum OpenACS Development: plpgsql help porting static-pages please

Subject: plsql help on static pages Here is a function that is causing an error:

create  function static_page__delete_stale_items (
                integer,        -- session_id   in 
                integer         -- package_id   in 
        ) returns integer as '
                p_session_id            alias for $1;
                p_package_id            alias for $2;
                v_root_folder_id        sp_folders.folder_id%TYPE;
                v_stale_file_row        static_pages.static_page_id%
                v_stale_folder_row      sp_folders.folder_id%TYPE;
                v_root_folder_id := static_page__get_root_folder

                -- First delete all files that are descendants of the 
root folder
                -- but aren''t in sp_extant_files

                for v_stale_file_row in
                        select static_page_id from static_pages
                                 where folder_id in (
                                select folder_id from sp_folders
                where tree_sortkey like ( select tree_sortkey || ''%''
                from sp_folders
                where folder_id = v_root_folder_id)
                       ) and static_page_id not in (
                                select static_page_id
                                from sp_extant_files
                               where session_id = p_session_id
                       PERFORM static_page__delete(v_stale_file_row);
                end loop;

                -- Now delete all folders that aren''t in 
sp_extant_folders.  There are two

 -- views created on the fly here: dead (all descendants of the 
                -- folder not in sp_extant_folders) and path (each 
folder and its depth).
                -- They are joined together to get the depth of all 
the folders that
                -- need to be deleted.  The root folder is excluded 
because it won''t
                -- show up in the filesystem search, so it will be 
missing from
                -- sp_extant_folders.
                for v_stale_folder_row in
                        select dead.folder_id from
                        (select folder_id from sp_folders
                                where (folder_id) not in (
                                        select folder_id
                                        from sp_extant_folders
                                        where session_id = 
                        ) dead,
                        (select tree_level(''folder_id'') as depth 
from sp_folders
                where tree_sortkey like ( select tree_sortkey || ''%''
                from sp_folders
                where folder_id = v_root_folder_id)
                        ) path
                        where dead.folder_id = path.folder_id
                                and dead.folder_id <> v_root_folder_id
                        order by path.depth desc
                        delete from sp_folders
                        where folder_id = v_stale_folder_row;

                        perform content_folder__delete
                end loop;
        return 0;
end;' language 'plpgsql';

I tried to figure out where in that area there could be an error. I suspect it is somewhere in the query, but I commented it out and still got the error message. Sorry about all these questions
Posted by Vinod Kurup on
Hi Dave,

I think the 'FOR var in SQL-STATEMENT' can only be used if 'var' is a record or a %ROWTYPE.

declare ... v_stale_file_row static_pages%ROWTYPE; or v_stale_file_row record; ... begin ... for v_stale_file_row in select ... loop perform static_page__delete(v.stale_file_row.static_page_id); end loop; ...
Posted by Dan Wickstrom on
What's the error message?

Divide and Conquer - Selectively comment out sections of the function until you can isolate it to a particular line.

The problem might be with the for loops.  I believe that you must use a row or record type for the for loop variable.

Posted by Don Baccus on
Yes, declare the for loop control variable as a %ROWTYPE then use the normal SQL dot-notation to pull your column out when using it within the loop.