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

Question

I have been trying to create a procedure in order to "EXECUTE INSERT INTO" a table. I gave up on this and continued trying to dynamically generate the required insert into code.

I have solved my issue by not creating the procedure and simply starting from the "DECLARE" bit; but still have not managed to make the pl/pgsql procedure to work.

The following procedure is not working:

CREATE PROCEDURE populate_xcc_allocatable() AS
    $body$
        DECLARE 
            TYPE tablearray IS VARRAY(17) OF VARCHAR2(30);
            xa_tables tablearray := tablearray('allocation', 'container', 'location', 'sap_posting', 'status');
            total integer;
        BEGIN
            total := xa_tables.count;
            FOR i IN 1..total
                LOOP
                    dbms_output.put_line('INSERT INTO allocatable VALUES (nextval(''allocatable_id_seq''), ''' || xa_tables(i) || ''');');
                END LOOP;
        END;
    $body$
LANGUAGE plpgsql;


LINE 4:  TYPE tablearray IS VARRAY(17) OF VARCHAR2(30);
CONTEXT: invalid type name "tablearray IS VARRAY(17) OF VARCHAR2(30)"

But this is working fine:

DECLARE 
    TYPE tablearray IS 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;
        FOR i IN 1..total
            LOOP
                dbms_output.put_line('INSERT INTO xcc_allocatable VALUES (nextval(''xcc_allocatable_id_seq''), ''' || xa_tables(i) || ''');');
            END LOOP;
    END;

Vao Tsun · Accepted Answer · 2016-07-25 17:53:48Z

3

dbms_output.put_line is oracle. and declare is oracle anonymous PL/SQL block

for postgres you should use raise info '%','some text'; instead of dbms_output.put_line('some text');

and instead of anonymous PL/SQL block use do statement, like

do
$$
declare
begin
end;
$$
;

to be frank, I think you get more help if you change tag postgres to oracle and plpgsql to plsql...

answered Jul 25, 2016 at 17:53

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abelisto · Accepted Answer · 2016-07-25 22:01:19Z

PostgreSQL have not procedures but functions returns void instead:

CREATE FUNCTION populate_xcc_allocatable() RETURNS void AS $body$

There is 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;
  i integer; -- Loop variables should be explicitly declared

To get array measurements use array functions:

BEGIN
  total := array_length(xa_tables, 1);
  FOR i in 1 .. total LOOP
    raise info 'INSERT INTO allocatable VALUES (nextval(''allocatable_id_seq''), ''%'');', xa_tables[i];
  END LOOP;

Any function should be finished by the RETURN:

  RETURN;
END $body$ language plpgsql;

Finally, the function you are trying to create can be replaced by the pure SQL:

INSERT INTO allocatable
  SELECT nextval('allocatable_id_seq'), x
  FROM unnest(array[
    'allocation', 'container', 'location',
    'spirit_portion', 'activity', 'asset_ownership',
    'container_location', 'sap_posting', 'status']) as t(x);

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related