Conditional CREATE Index for Postgresql and Oracle

In order to create an index conditionally (e.g. only, if it does not exist) in postgresql or in oracle, one can use the following two idioms

Postgres

create or replace function inline_0() returns integer as '

declare v_exists integer;
begin
select into v_exists count(*) from pg_class where relname = ''acs_permissions_object_id_idx'';
if v_exists = 0 then
create index acs_permissions_object_id_idx on acs_permissions(object_id);
end if;
return null;
end;' language 'plpgsql'

select inline_0();
drop function inline_0();

Oracle

declare v_exists integer;

begin
select count(*) into v_exists from user_indexes where lower(index_name)='acs_permissions_object_id_idx';
if v_exists = 0 then
execute immediate 'create index acs_permissions_object_id_idx on acs_permissions(object_id)';
end if;

end;
/
show errors

These examples were posted by Dave Bauer OpenACS Development forum and can be used in a similar style for conditionally creating tables, etc.

Popular tags

17 (1), 5.10 (1), 5.10.0 (2), 5.10.1 (1), 5.9.0 (3), 5.9.1 (4), ad_form (1), ADP (1), ajax (1), aolserver (1), asynchronous (2), bgdelivery (3), bootstrap (1), bugtracker (1), CentOS (1), COMET (1), compatibility (1), CSP (1), CSRF (1), cvs (3), debian (7), docker (2), docker-compose (1), emacs (2), engineering-standards (1), exec (1), fedora (1), FreeBSD (1), guidelines (1), host-node-map (1)

No registered users in community xowiki
in last 30 minutes

Contributors

OpenACS.org

Categories: Subsystems Documentation (OpenACS.org)