Postgresql: Create trigger in postgresql using pl/pgsql to fire cluster command after insert statement

Create trigger in postgresql using pl/pgsql to fire cluster command after insert statement.

The result of assesment: In summary it isnt possible to fire a DDL from a DML trigger.

Trigger notes:

Trigger can fire the defined function before or after the trigger condition is met (trigger condition can be insert or update or delete or truncate). 
INSTEAD OF keyword applies only to view, allows only FOR EACH ROW operation only. In case of view with before or after trigger can only hold FOR EACH STATEMENT.
Trigger can fire for each row or statement as well (meeting condition).
Trigger can also be fired for Truncate statement though only for each statement.
Multiple triggers for the same relation, then the trigger will be fired in alphabetical order.

create or replace trigger TRG_WBACLUSTONDML
AFTER INSERT ON pgtst_schema.pgtst_tbl1_random
FOR EACH STATEMENT
EXECUTE PROCEDURE FUNC_wbaclustondml();

To get the list of triggers created on the current database:

pgtst_db=> \c
You are now connected to database "pgtst_db" as user "pgtst_usr".
pgtst_db=>
pgtst_db=> select * from information_schema.triggers;
-[ RECORD 1 ]--------------+---------------------------------------------------
trigger_catalog            | pgtst_db
trigger_schema             | pgtst_schema
trigger_name               | trg_wbaclustondml
event_manipulation         | INSERT
event_object_catalog       | pgtst_db
event_object_schema        | pgtst_schema
event_object_table         | pgtst_tbl1_random
action_order               | 1
action_condition           |
action_statement           | EXECUTE FUNCTION pgtst_schema.func_wbaclustondml()
action_orientation         | STATEMENT
action_timing              | AFTER
action_reference_old_table |
action_reference_new_table |
action_reference_old_row   |
action_reference_new_row   |
created                    |
pgtst_db=>

Test the routine, load data to the table....

insert into pgtst_tbl1_random
SELECT (id+10000000) idcol,name
FROM pgtst_tbl1
ORDER BY random()
limit 1000;

Actual output:

pgtst_db=> insert into pgtst_tbl1_random
pgtst_db-> SELECT (id+10000000) idcol,name
pgtst_db-> FROM pgtst_tbl1
pgtst_db-> ORDER BY random()
pgtst_db-> limit 1000;
ERROR:  cannot CLUSTER "pgtst_tbl1_random" because it is being used by active queries in this session
CONTEXT:  SQL statement "cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey"
PL/pgSQL function func_wbaclustondml() line 3 at EXECUTE
pgtst_db=> \q

The trigger we build is now failing for obvious reason; which is we cant fire a ddl on the table undergoing DML tx.
CLUSTER is a ddl command.

Function defination before:

CREATE FUNCTION FUNC_wbaclustondml() RETURNS trigger AS $$
    BEGIN
execute format('cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey');
analyze verbose pgtst_tbl1_random;
    END;
$$ LANGUAGE plpgsql;

now let us change the function defination:

CREATE or replace FUNCTION FUNC_wbaclustondml() RETURNS trigger AS $$
    BEGIN
analyze verbose pgtst_tbl1_random;
    END;
$$ LANGUAGE plpgsql;

Actual output:

pgtst_db=> CREATE or replace FUNCTION FUNC_wbaclustondml() RETURNS trigger AS $$
pgtst_db$>     BEGIN
pgtst_db$>
pgtst_db$> analyze verbose pgtst_tbl1_random;
pgtst_db$>     END;
pgtst_db$> $$ LANGUAGE plpgsql;
CREATE FUNCTION

pgtst_db=> insert into pgtst_tbl1_random
pgtst_db-> SELECT (id+10000000) idcol,name
pgtst_db-> FROM pgtst_tbl1
pgtst_db-> ORDER BY random()
pgtst_db-> limit 1000;
INFO:  analyzing "pgtst_schema.pgtst_tbl1_random"
INFO:  "pgtst_tbl1_random": scanned 30000 of 105904 pages, containing 2833176 live rows and 273 dead rows; 30000 rows in sample, 10001489 estimated total rows
ERROR:  control reached end of trigger procedure without RETURN
CONTEXT:  PL/pgSQL function func_wbaclustondml()
pgtst_db=> commit;
WARNING:  there is no transaction in progress
COMMIT
pgtst_db=>

Look now the trigger fired and stats update is complete.

pgtst_db=> select relname,to_char(reltuples,'999999999999999') from pg_class where relname='pgtst_tbl1_random';
-[ RECORD 1 ]--------------
relname | pgtst_tbl1_random
to_char |         10001489

We have 1000 more records in the table.

In summary it isnt possible to fire a DDL from a DML trigger.