How to call procedure inside procedure in PostgreSQL?

by haylee.mertz , in category: PHP Databases , 2 years ago

How to call procedure inside procedure in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

by dalton_moen , a year ago

@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.

Member

by dedrick , 5 months ago

@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.