问我如何链接一个真空清除程序运行与pg_cron？

EN

CREATE OR REPLACE FUNCTION dba.purge_event_log (
    retain_days_in integer_positive default 14)

RETURNS int4

AS $BODY$

WITH  -- Use a CTE so that we've got a way of returning the count easily.
deleted AS (

-- Normal-looking code for this requires a literal:
-- where your_dts < now() - INTERVAL '14 days'
-- Don't want to use a literal, SQL injection, etc.
-- Instead, using a interval constructor to achieve the same result:

   DELETE
     FROM dba.event_log
    WHERE dts < now() - make_interval (days => $1)
RETURNING *
),

----------------------------------------
-- Save details to a custom log table
----------------------------------------
logit AS (
insert into dba.event_log (name, details)
    values ('purge_event_log(' || retain_days_in::text || ')',
             'count = ' || (select count(*)::text from deleted)
           )
)

----------------------------------------
-- Return result count
----------------------------------------
select count(*) from deleted;

$BODY$
  LANGUAGE sql;

COMMENT ON FUNCTION dba.purge_event_log (integer_positive) IS
'Delete dba.event_log records older than the day count passed in, with a default retention period of 14 days.';

Command                Limitation
CREATE DATABASE
ALTER DATABASE         If creating a new table space.
DROP DATABASE
CLUSTER                Without any parameters.
CREATE TABLESPACE
DROP TABLESPACE
REINDEX                All in system catalogs, database, or schema.

CREATE SUBSCRIPTION    When creating a replication slot (the default behavior.)
ALTER SUBSCRIPTION     With refresh option as true.
DROP SUBSCRIPTION      If the subscription is associated with a replication slot.

COMMIT PREPARED
ROLLBACK PREPARED
DISCARD ALL

VACUUM

/*
select * from dba.vacuum_sql_function();

Fails:
ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL function "vacuum_sql_function" statement 1. 0.000 seconds. (Line 13).
*/

DROP FUNCTION IF EXISTS dba.vacuum_sql_function();

CREATE FUNCTION dba.vacuum_sql_function()
RETURNS VOID
LANGUAGE sql

AS $sql_code$

VACUUM ANALYZE activity;

$sql_code$;

select * from dba.vacuum_sql_function(); -- Fails.

/*
select * from dba.vacuum_plpgsql_function();

Fails:
ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL statement "VACUUM ANALYZE activity"
PL/pgSQL function vacuum_plpgsql_function() line 4 at SQL statement. 0.000 seconds. (Line 22).
*/

DROP FUNCTION IF EXISTS dba.vacuum_plpgsql_function();

CREATE FUNCTION dba.vacuum_plpgsql_function()
RETURNS VOID
LANGUAGE plpgsql

AS $plpgsql_code$

BEGIN
VACUUM ANALYZE activity;
END

$plpgsql_code$;

select * from dba.vacuum_plpgsql_function();

/*
call dba.vacuum_sql_procedure();

ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL function "vacuum_sql_procedure" statement 1. 0.000 seconds. (Line 20).
*/

DROP PROCEDURE IF EXISTS dba.vacuum_sql_procedure();

CREATE PROCEDURE dba.vacuum_sql_procedure()
LANGUAGE SQL

AS $sql_code$

VACUUM ANALYZE activity;

$sql_code$;

call dba.vacuum_sql_procedure();

 /*
call dba.vacuum_plpgsql_procedure();

ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL statement "VACUUM ANALYZE activity"
PL/pgSQL function vacuum_plpgsql_procedure() line 4 at SQL statement. 0.000 seconds. (Line 23).
*/

DROP PROCEDURE IF EXISTS dba.vacuum_plpgsql_procedure();

CREATE PROCEDURE dba.vacuum_plpgsql_procedure()
LANGUAGE plpgsql

AS $plpgsql_code$

BEGIN
VACUUM ANALYZE activity;
END

$plpgsql_code$;

call dba.vacuum_plpgsql_procedure();

CREATE TABLE IF NOT EXISTS dba.vacuum_list (
    database_name   text,
    schema_name     text,
    table_name      text,
    run             boolean,
    run_analyze     boolean,
    run_full        boolean,
    last_run_dts    timestamp)

ALTER TABLE dba.vacuum_list ADD CONSTRAINT
   vacuum_list_pk
   PRIMARY KEY (database_name, schema_name, table_name);