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();

Your function returns an integer, not a table. It is unclear exactly what you want to do, but a scalar function would be called as select count_function(). — Gordon Linoff
– Gordon Linoff, Commented Oct 11, 2016 at 12:40
Apart from anything else, you need to add "RETURN Cnt;" before the END statement... — mlinth
– mlinth, Commented Oct 11, 2016 at 12:42
I would like to define the count value as a variable (integer). I want to use this value to trigger an event - when the count = 500. So I do want to return an integer value. — L Marfell
– L Marfell, Commented Oct 11, 2016 at 12:44
@a_horse_with_no_name I tried executing the procedure in save_table2() as suggested in the answer by pid, but I get an error at EXECUTE PROCEDURE — L Marfell
– L Marfell, Commented Oct 11, 2016 at 13:16

pid · Accepted Answer · 2016-10-11 13:59:51Z

4

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.

edited Oct 11, 2016 at 13:59

answered Oct 11, 2016 at 12:45

pid

11.6k6 gold badges37 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

L Marfell Over a year ago

ah yes this does work, although can I call a function as a variable in a trigger?

pid Over a year ago

Yes, you can call a function from another function and store the result in a variable.

mlinth Over a year ago

The only thing I'd add is that I personally would store the value 500 in a table, so you can change it easily without having to edit the function.

L Marfell Over a year ago

@pid Thanks for your help everyone! I tried the above code, but unfortunately it doesn't work. Executing the procedure within the trigger causes an error near the WHEN count_function() and executing in the function brings an syntax error at EXECUTE PROCEDURE.

L Marfell Over a year ago

I just noticed this is a procedure rather than function? what's the difference between the two? I tried function that didn't work. Trying procedure I get an error at REPLACE PROCEDURE

|

Sebz · Accepted Answer · 2016-10-11 12:47:17Z

0

You should call the function not the variable from the function: SELECT count_function () Also in the function you do not need the variable and have this: RETURN (SELECT COUNT(*) FROM table1);

answered Oct 11, 2016 at 12:47

Sebz

5122 silver badges4 bronze badges

12 Comments

L Marfell Over a year ago

Yes it does thanks! I'm now just stuck on how to use this function value to trigger an event

Sebz Over a year ago

In Postgres there are trigger function that you can use:

CREATE OR REPLACE FUNCTION trigger_name()   RETURNS trigger AS $$   BEGIN       IF count_function()>= 500 THEN          insert into table...;       END IF;     RETURN NEW;   END;   $$   LANGUAGE 'plpgsql';

Sebz Over a year ago

and create the trigger:CREATE TRIGGER copy_trigger AFTER INSERT ON table1 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_function(); In my previous comment, the name of the function is wrong, it should be trigger_function() instead of trigger_name

L Marfell Over a year ago

Ok so I tried the above trigger function along with trigger: CREATE TRIGGER save_table2_trigger AFTER INSERT OR UPDATE ON table1 FOR EACH STATEMENT EXECUTE PROCEDURE save_table2(); But I got the error ... invalid input syntax for integer "new.type"

Sebz Over a year ago

that error is saying that something is expected to be integer, but it's probably varchar, in that case you should do an explicit cast ''::integer;

|

Collectives™ on Stack Overflow

Declare COUNT value as variable (PostgreSQL)

2 Answers 2

8 Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related