Declare COUNT value as variable (PostgreSQL)

Question

Does anyone know if it possible to declare the COUNT value as a variable to call in queries/functions/triggers?

I would like to use the COUNT value to trigger data transfer from table1 to table2, triggering when the row count of table1 reaches 500.

FIX.....

Defining count function:

    CREATE OR REPLACE FUNCTION count_function () RETURNS integer AS $$  
     BEGIN
     RETURN (SELECT COUNT(*) FROM table1);
    END $$ LANGUAGE plpgsql;

Calling the variable to trigger an event:

    CREATE OR REPLACE FUNCTION save_table2()
    RETURNS trigger AS
    $$
    BEGIN
    IF count_function()>=500 THEN
    INSERT INTO table2
    values ('NEW.column1','NEW.column2');
    END IF;
    RETURN NEW;
    END $$
    LANGUAGE plpgsql;

    CREATE TRIGGER copy_trigger 
    AFTER INSERT ON table1 
    FOR EACH ROW
    EXECUTE PROCEDURE save_table2();

Answer 1

Have you tried this (should work on MySQL and SQL Server, maybe PostgreSQL, too)?

SELECT count_function();

On Oracle it would be

SELECT count_function() FROM DUAL;

To store the result in a variable you can do this:

DECLARE result int;

SET result = SELECT count_function();

In your case the trigger can be written as:

CREATE TRIGGER copy_trigger 
AFTER INSERT ON table1 
FOR EACH STATEMENT 
WHEN count_function() >= 500
EXECUTE PROCEDURE save_table2 ();

Notice that >= means greater or equal. While => does not exist (or is not what it looks like).

If nothing else helps, you can do this:

CREATE OR REPLACE FUNCTION save_table2_on_500()
RETURNS VOID AS $$
  DECLARE cnt INTEGER;
BEGIN

    cnt := (SELECT COUNT(*) FROM table1);

    IF cnt >= 500 THEN
        EXECUTE PROCEDURE save_table2();
    END IF;

END $$ LANGUAGE plpgsql;

CREATE TRIGGER copy_trigger_on_500
AFTER INSERT ON table1
FOR EACH STATEMENT
EXECUTE PROCEDURE save_table2_on_500();

EDIT: What was wrong with the code

I've used the keyword PROCEDURE because it is very common on various database systems (SQL Server, Oracle, MySQL). But it is not legit on PostgreSQL.

On PostgreSQL you must use FUNCTION and specify the return type VOID, which I think is kind of a contradiction, but I'm digressing on details here.

The full explanation of function vs procedure is here.

The difference is mainly that a function returns always a scalar value while a procedure may return nothing (VOID), a scalar value or a data table. It is more flexible but also has other caveats. Refer to the link above for more details.