postgresql - Cannot create procedure in pl/pgsql but code works without create procedure -

i have been trying create procedure in order "execute insert into" table. gave on , continued trying dynamically generate required insert code.

i have solved issue not creating procedure , starting "declare" bit; still have not managed make pl/pgsql procedure work.

the following procedure not working:

create procedure populate_xcc_allocatable()     $body$         declare              type tablearray varray(17) of varchar2(30);             xa_tables tablearray := tablearray('allocation', 'container', 'location', 'sap_posting', 'status');             total integer;         begin             total := xa_tables.count;             in 1..total                 loop                     dbms_output.put_line('insert allocatable values (nextval(''allocatable_id_seq''), ''' || xa_tables(i) || ''');');                 end loop;         end;     $body$ language plpgsql;   line 4:  type tablearray varray(17) of varchar2(30); context: invalid type name "tablearray varray(17) of varchar2(30)" 

but working fine:

declare      type tablearray varray(17) of varchar2(30);     xa_tables tablearray := tablearray('allocation', 'container', 'location', 'spirit_portion', 'activity', 'asset_ownership', 'container_location', 'sap_posting', 'status');         total integer;     begin         total := xa_tables.count;         in 1..total             loop                 dbms_output.put_line('insert xcc_allocatable values (nextval(''xcc_allocatable_id_seq''), ''' || xa_tables(i) || ''');');             end loop;     end; 

postgresql have not procedures functions returns void instead:

create function populate_xcc_allocatable() returns void $body$ 

there no 'local types', use array type instead:

declare   xa_tables text[] := array[     'allocation', 'container', 'location',     'spirit_portion', 'activity', 'asset_ownership',     'container_location', 'sap_posting', 'status'];   total integer;   integer; -- loop variables should explicitly declared 

to array measurements use array functions:

begin   total := array_length(xa_tables, 1);   in 1 .. total loop     raise info 'insert allocatable values (nextval(''allocatable_id_seq''), ''%'');', xa_tables[i];   end loop; 

any function should finished return:

  return; end $body$ language plpgsql; 

finally, function trying create can replaced pure sql:

insert allocatable   select nextval('allocatable_id_seq'), x   unnest(array[     'allocation', 'container', 'location',     'spirit_portion', 'activity', 'asset_ownership',     'container_location', 'sap_posting', 'status']) t(x);