Can PostgreSQL declare a procedure inside a procedure?

Question

create or replace procedure procedure_1()
language plpgsql
as $$ 
declare 
    precedure procedure_2()
    begin 
        select 1;
    end
begin
    select 1;
end; $$

Is there any way to declare a procedure_2() inside procedure_1()?

Answer 1

Yes - if by "declare" you mean "create".

CREATE OR REPLACE PROCEDURE procedure_1(INOUT result int)
  LANGUAGE plpgsql AS
$proc1$
BEGIN
   CREATE OR REPLACE PROCEDURE procedure_2(INOUT result int)
     LANGUAGE plpgsql AS
   $proc2$
   BEGIN
      result := 2;
   END
   $proc2$;
   
   result := 1;
END
$proc1$;

db<>fiddle here

You just have to get the quoting right. See:

• What are '$$' used for in PL/pgSQL
• Insert text with single quotes in PostgreSQL

Functions and procedures are not "declared", but "created" in Postgres. That creates an object in the database which is then visible and usable by all with appropriate permissions. (Not just a temporary object local to the procedure or transaction.)

You can, however, create a "temporary" function or procedure, with this "hack" - if that's what you had in mind:

• How to create a temporary function in PostgreSQL?