title: How to call a stored procedure inside another stored procedure in postgreSQL
body: <p>I have a two tables, <code>table_version_1</code> and <code>table_version_2</code>, I am trying to generate a new table based on these two tables. For which I have to write 4 stored procedures, 
   <code>procedure1</code>, <code>procedure2</code>,  <code>procedure3</code>,  <code>procedure4</code></p>
  
  <p>I am triggering <code>procedure1</code> from my application </p>
  
  <pre><code> select * from procedure1
  </code></pre>
  
  <p>I am calling <code>procedure2</code> in <code>procedure1</code> </p>
  
  <pre><code> PERFORM procedure2
  </code></pre>
  
  <p>and I am calling <code>procedure3</code> in <code>procedure2</code> </p>
  
  <pre><code>  PERFORM procedure3
  </code></pre>
  
  <p>and calling <code>procedure4</code> in <code>procedure3</code></p>
  
  <pre><code>  PERFORM procedure3
  </code></pre>
  
  <p>My stored procedure looks something like below.</p>
  
  <pre><code>    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 &lt;&gt; 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$;
  </code></pre>
  
  <p>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 <code>return</code>  all my steps <code>create query, insert query and select query</code> are failing and if I write <code>return</code> then it is not going to  <code>procedure2</code>. What is the correct process to run this procedure?</p>