/* 
To capture pg_stat_activity item for current command for
auditing, spying or review.  Lots of garbage collection and 
further analysis in table slog.

Logging and auditing is usually done by triggers or rules.
In this case we want to grab the pg_stat_activity data
in the middle of the query. The lateral join is implicitly 
on pg_backend_pid().

As you can see, the lateral join is not appropriate for UPDATES and
INSERTS.  The slog() function can be called in the 
FROM clause in those cases.
*/

/* Create some data */
DROP TABLE IF EXISTS abc;
DROP TABLE IF EXISTS slog cascade;
DROP FUNCTION IF EXISTS slog();

/* A useful no usable content table */
CREATE TABLE abc (
	cola	int,
	colb	int,
	colc	int);
INSERT INTO abc (cola) SELECT generate_series(1,25);
UPDATE abc SET colb = cola +2, colc = cola + 7;

/* create table for tracking pg_stat_activity */
CREATE TABLE slog (
	usename	name,
	datname name,
  application_name text,
	state text,
  query	text
);

CREATE OR REPLACE FUNCTION slog()
RETURNS VOID LANGUAGE plpgsql AS
$$
DECLARE
BEGIN
		INSERT INTO slog (usename, datname, application_name,state, query)
		SELECT current_user, datname, application_name, state, query
		FROM pg_stat_activity where pg_backend_pid() = pid
			AND usename = current_user and backend_type = 'client backend';
END;
$$;


SELECT cola, colb, colc FROM abc CROSS JOIN LATERAL slog() ;

UPDATE abc SET colc = colc + 1
	FROM slog()
	WHERE cola > 50;

INSERT INTO abc (cola, colb, colc) 
	SELECT 1000,1002,1007 from slog();

SELECT * from abc;
SELECT * from slog;