./klaus hofeditz :: weblog

Sample Script

create or replace function inline_0 ()
returns integer as '
declare
        v_count         integer;
begin
        select count(*) into v_count from user_tab_columns
        where lower(table_name) = ''acs_object_types'' and lower(column_name) = ''icon_path'';
        IF v_count > 0 THEN return 1; END IF;

        alter table acs_object_types add column icon_path character varying(100);
        RETURN 0;

end;' language 'plpgsql';
select inline_0 ();
drop function inline_0 ();

Check existence of column:

select count(*) into v_count from user_tab_columns where table_name = ''IM_HOURS'' and column_name = ''INVOICE_ID'';

Check existence of table:

select count(*) into v_count from pg_tables where tablename = ''im_capacity_planning'';

Check for existing constraints

        select count(*) into v_count from pg_constraint
        where lower(conname) = ''im_hours_project_fk'';

Create categories

SELECT im_category_new (16006, 'Rejected', 'Intranet Absence Status');

Create menu item

SELECT im_new_menu(
        'intranet-timesheet2',
        'timesheet2_absences_bankholiday',
        'New Bank Holiday',
        '/intranet-timesheet2/absences/new?absence_type_id=5004',
        50,
        'timesheet2_absences',
        null
);

Create menu permissions

SELECT im_new_menu_perms('timesheet2_absences_bankholiday', 'Employees');