New answers tagged

0

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.


2

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[]) LANGUAGE plpgsql AS $$ DECLARE rec RECORD; v timestamp; BEGIN FOR rec IN select table_schema, table_name from information_schema.tables where table_catalog = any (...


1

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.


1

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 transaction" It's still ...


2

The BEGIN; 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 ...


Top 50 recent answers are included