You must use a function, not a procedure:
CREATE FUNCTION pro_update_last_read() RETURNS trigger ...
The trigger must be defined FOR EACH ROW, and you cannot pass columns to the trigger function.
The way you access the columns in the trigger function is via the NEW variable: NEW.sensor_id and NEW.report_time.
The best solution is to not pass a single varchar parameter but an array:
CREATE OR REPLACE PROCEDURE bhuvi(p_catalogs text, p_tbls text)
FOR rec IN select table_schema, table_name
where table_catalog = any (...
PostgreSQL has several languages for functions, and it is easy to get confused.
PL/pgSQL uses BEGIN / END blocks.
But LANGUAGE sql functions consist only of SQL statements with no blocks ir other procedural elements.
So either change the language to plpgsql and add a semicolon or (better) remove the BEGIN / END block.
After establishing that BEGIN; is not allowed in a procedure and that the client-side CALL must not occur from an already started transaction, the question boils down to (as you wrote as a comment):
"does that mean it's not possible to return a cursor from a
procedure as (in my understanding) they don't survive past their
It's still ...
is wrong and will cause an error. You cannot start a transaction inside a procedure, because there is already an active transaction.
You can end a transaction, which implies that a new transaction is started immediately.
Without the BEGIN;, your procedure works just fine.
The problem must be with the way you are calling it. As the ...