CODEWITHSUNDEEP
sqlpostgresqlplpgsqldatabase-trigger
DumbLoawai
DumbLoawai

Reputation: 11

Postgresql function has unsupported return type

I got an error while trying to update specific column values in my table

ERROR: function "my_function" in FROM has unsupported return type trigger

I tried to run the update script manually but it gave me an error to write an after trigger because the database is being used in production. Basically what i want to do is to update one table columns values from another table and i named that table as "table_name_temp". The script to me looks fine but I am not sure what exact the problem could be with it because the error response seems to be very opaque. 

 Create or REPLACE FUNCTION my_function() RETURNS TRIGGER AS $$
      BEGIN
    update schema_name.table_name set category = schema_name.table_name_temp.category 
        FROM schema_name.table_name_temp 
        WHERE (some where clauses for first check)
        and schema_name.table_name.name = schema_name.table_name_temp.name
        returning null;
      END;
   $$ LANGUAGE plpgsql;

 DROP TRIGGER IF EXISTS trigger_name on schema_name.table_name;

   CREATE TRIGGER trigger_name AFTER UPDATE ON schema_name.table_name
    FOR EACH ROW EXECUTE PROCEDURE my_function();

select * from my_function();

Upvotes: 0

Views: 1439

Answers (1)

JGH
JGH

Reputation: 17836

You can't call a trigger function directly, it has to be called by the trigger.

Usually, trigger functions contains a NEW and and OLD record. These variables are set by the trigger and are readily available in the trigger function. If you call the trigger function directly, nothing is setting these values.

To use the same piece of code from a trigger and from a query, it has to be in a separate function:

CREATE or REPLACE FUNCTION my_function_that_do_something() RETURNS VOID AS $$
      BEGIN
          update ...;  --no need to return anything
      END;
   $$ LANGUAGE plpgsql;


CREATE or REPLACE FUNCTION my_trigger_function() RETURNS TRIGGER AS $$
      BEGIN
          PERFORM my_function_that_do_something();
          return null;
      END;
   $$ LANGUAGE plpgsql;


CREATE TRIGGER trigger_name AFTER UPDATE ON schema_name.table_name
    FOR EACH ROW EXECUTE PROCEDURE my_trigger_function();

SELECT my_function_that_do_something();

Upvotes: 1

Related Questions