Search · 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 Month June 2017
Sun Mon Tue Wed Thu Fri Sat
28 29 30 31 (4) 1 (3) 2 3
4 (1) 5 (3) 6 (9) 7 8 9 10
(2) 11 12 (5) 13 14 (1) 15 (7) 16 (7) 17
(7) 18 19 (3) 20 21 (3) 22 23 24
25 26 27 28 29 30 1

Popular tags

17 , 5.9.0 , 5.9.1 , ad_form , ADP , ajax , aolserver , asynchronous , bgdelivery , bootstrap , bugtracker , CentOS , COMET , CSP , CSRF , cvs , debian , emacs , fedora , FreeBSD , host-node-map , hstore , includelets , install , installation , installers , install-ns , javascript , libthread , linux
No registered users in community xowiki
in last 30 minutes
Contributors

OpenACS.org