How to call procedure inside procedure in PostgreSQL?

@haylee.mertz 

To call a procedure inside another procedure in PostgreSQL, you can use the following syntax:

1

SELECT * FROM my_procedure();

Replace my_procedure with the name of the procedure you want to call.

Here's an example of a procedure that calls another procedure:

1
2
3
4
5
6
7
8

CREATE OR REPLACE PROCEDURE parent_procedure()
AS $$
BEGIN
    -- Do some work here
    SELECT * FROM child_procedure();
    -- Do some more work here
END;
$$ LANGUAGE plpgsql;

In this example, the parent_procedure calls the child_procedure using the SELECT statement. The child_procedure is executed as part of the SELECT statement, and the results are discarded (since they are not being stored in a variable or used in any other way).

Note that you can also pass parameters to the called procedure, just like you would with any other SELECT statement. For example:

1

SELECT * FROM child_procedure(10, 'abc');

This would call the child_procedure and pass it the values 10 and 'abc' as parameters.

@haylee.mertz 

In PostgreSQL, you can call a procedure inside another procedure using the EXECUTE statement. Here's an example:

CREATE OR REPLACE PROCEDURE parent_procedure() AS $$ BEGIN -- Do some work here

1
2
3

EXECUTE 'CALL child_procedure()';

-- Do some more work here

END; $$ LANGUAGE plpgsql;

In this example, the parent_procedure calls the child_procedure using the EXECUTE statement, which allows you to execute dynamic SQL statements. The CALL statement is used to invoke the child_procedure.

You can also pass parameters to the called procedure using the EXECUTE statement. For example:

CREATE OR REPLACE PROCEDURE parent_procedure(param1 integer, param2 text) AS $$ BEGIN -- Do some work here

1
2
3

EXECUTE 'CALL child_procedure($1, $2)' USING param1, param2;

-- Do some more work here

END; $$ LANGUAGE plpgsql;

In this example, the parent_procedure takes two parameters, param1 and param2. These parameters are passed to the child_procedure using the USING clause in the EXECUTE statement. The $1 and $2 placeholders are used to represent the values of param1 and param2, respectively.