View · Index

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.

previous March 2024
Sun Mon Tue Wed Thu Fri Sat
25 26 27 28 29 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31 1 2 3 4 5 6

Popular tags

17 , 5.10 , 5.10.0 , 5.9.0 , 5.9.1 , ad_form , ADP , ajax , aolserver , asynchronous , bgdelivery , bootstrap , bugtracker , CentOS , COMET , compatibility , CSP , CSRF , cvs , debian , docker , docker-compose , emacs , engineering-standards , exec , fedora , FreeBSD , guidelines , host-node-map , hstore
No registered users in community xowiki
in last 30 minutes
Contributors

OpenACS.org