How to call a stored procedure inside another stored procedure in postgreSQL

Question

I have a two tables, table_version_1 and table_version_2, I am trying to generate a new table based on these two tables. For which I have to write 4 stored procedures, procedure1, procedure2, procedure3, procedure4

I am triggering procedure1 from my application

 select * from procedure1

I am calling procedure2 in procedure1

 PERFORM procedure2

and I am calling procedure3 in procedure2

  PERFORM procedure3

and calling procedure4 in procedure3

  PERFORM procedure3

My stored procedure looks something like below.

    CREATE OR REPLACE FUNCTION procedure1(tb_name, compare_tb_name, file_version, compare_file_version)
    RETURNS text
        LANGUAGE 'plpgsql'

        COST 100
        VOLATILE 
    AS $BODY$
    DECLARE

          createquery text;
          fpo_data jsonb;
          inddata jsonb;

          f_primary_key text;
          rowscount INTEGER;
          datacount INTEGER;
          fcdata1 text;
          fid INTEGER;

    BEGIN

        createquery := 'CREATE TABLE IF NOT EXISTS ' || tb_name || '_' || file_version || '_' || compare_file_version || '_pk(
           id serial PRIMARY KEY,
           fc_pkey1 VARCHAR (250)  NULL,
           fc_pkey2  VARCHAR (250)  NULL,
           fc_pkey3  VARCHAR (250)  NULL,
           fpo_data TEXT NULL,
           fid INTEGER NULL
        )';

        EXECUTE createquery;

        EXECUTE 'SELECT count(*) FROM ' || tb_name || '_' || file_version || '_' || compare_file_version || '_pk' INTO rowscount; 
        EXECUTE 'SELECT count(*) FROM ' || tb_name INTO datacount; 

        IF(rowscount <> datacount) THEN

            EXECUTE 'SELECT json_agg((fpdata, foseqid))::jsonb
            FROM (SELECT fo_data AS fpdata, fo_seq_id as foseqid
                FROM '||tb_name||'
                LIMIT 1000
            ) t'  INTO fpo_data;

        FOR inddata IN SELECT * FROM jsonb_array_elements(fpo_data) LOOP

            EXECUTE 'INSERT INTO ' || tb_name || '_' || file_version || '_' || compare_file_version || '_pk(fc_pkey1, fpo_data, fid) VALUES ($1, $2, $3)' USING f_primary_key, inddata, fid;

        END LOOP;


        ELSE

            PERFORM procedure2(tb_name, compare_tb_name, file_version, compare_file_version);

        END IF;

        return 'Primary Key Generation completed';

        END;
    $BODY$;

I have not written the complete query. I just written important steps of my query. My issue is that, in the above query I have create query, insert query and select query and at the end of the stored procedure I have written return statement. If I remove the return all my steps create query, insert query and select query are failing and if I write return then it is not going to procedure2. What is the correct process to run this procedure?

How to call a stored procedure inside another stored procedure in postgreSQL

0 Answers0