Get response from EXECUTE in PLPGSQL

Question

I have set up a small query that uses a DO block to dynamically edit multiple tables.

DO
$$
DECLARE
    _table varchar[];
    loop_item text;
BEGIN
    SELECT array_agg(table_name::TEXT) FROM information_schema.tables 
    INTO _table
    WHERE table_schema = 'public';

FOREACH loop_item IN ARRAY "_table" 
LOOP
    IF loop_item != 'test' THEN     
        EXECUTE format('ALTER TABLE %s
        ADD COLUMN IF NOT EXISTS new_column varchar;', loop_item);
    END IF;
END LOOP;
END;
$$

If the EXECUTE statement is not executed, a notice is printed. But I would also like to know when it was executed successfully. How can I capture and RAISE the return message of the EXECUTE statement?

Info: I am using the Azure Database for Postgres and have no access to the config files.

score 0 · Accepted Answer · answered Oct 01 '21 at 14:31

0

Raise a NOTICE:

IF loop_item != 'test' THEN     
    EXECUTE format('ALTER TABLE %I
        ADD COLUMN IF NOT EXISTS new_column varchar;', loop_item);
    RAISE NOTICE 'Table %s altered', loop_item;
END IF;

Your original code is vulnerable to SQL injection, and you should use the %I format.

answered Oct 01 '21 at 14:31

Laurenz Albe

209,280
17
206
263

My question was inaccurate. This works for ALTER TABLE, but I am interested if it is possible to grab the output of e.g. a SELECT or INSERT and then raise it? So I can run one query conveniently on many tables. – riderinred Oct 04 '21 at 14:26
`EXECUTE 'SELECT ...' INTO record_var; RAISE '%', to_json(record_var);` – Laurenz Albe Oct 04 '21 at 19:38

Get response from EXECUTE in PLPGSQL

1 Answers1