Execute anonymous code from stored procedure

1. The do block tries to modify a p_out that hasn't been defined in it or in the procedure you want to run that block.

2. If p_out is a typo and you wanted to modify the v_out you declared in the procedure, that won't be possible: the do block doesn't have access to it.

3. You can't return things from a do block the way procedures can via out/inout parameters.

4. PL/pgSQL execute doesn't have an out clause. It does offer an into clause to write its result to a variable.

5. PostgreSQL has a boolean type. You don't need to use a varchar that says 'true' like in Oracle before 23c.

6. You can declare a procedure that accepts records of a specific table but that's pretty impractical seeing that you can't combine the call with a select that would get you the row. You'd need to use more dynamic sql wrappers to fetch the row outside and inject it as a literal matching the row type, or bind/type out the literals as the parameter yourself.

7. This:

   select count(1) into id_cnt 
   from emp 
   where id = $1;
   
   if(id_cnt>0)
   

   Sounds like you just want to check if any such row exists:

   if exists(select from emp where id = $1)
   

   That's shorter and faster. It does minimum work required to establish that, without having to count exactly how many rows meet the criteria.

8. Make the procedure accept an int that corresponds to the tbl_tokens.id, then select the tokens from inside the procedure based on that.

9. If you want the code in tokens to return something, you'll need to communicate via side-effects (make it write to a pre-defined temp table, then read those results from there after the call). Alternatively, you can replace the do blocks with a create statement of a temp procedure/function, followed by a call/select that invokes it.

_{demo at db<>fiddle}

create table emp(id)as values(1::int),(2),(3);

create table tbl_tokens (id int,tokens text);

insert into tbl_tokens values(1,$tokens$
  drop function if exists pg_temp.f(public.emp.id%type);
  create function pg_temp.f(arg public.emp.id%type)
    returns boolean return exists(select from emp where id = arg); 
  select pg_temp.f($1);
  $tokens$);

create function public.prc_token(in_token_id public.tbl_tokens.id%type) 
  returns boolean as $procedure$
declare v_out boolean;
begin
  execute(select tokens from tbl_tokens where id=in_token_id)into v_out using 1;
  return v_out;
end $procedure$ language plpgsql;

select public.prc_token(1);