JavaEar 专注于收集分享传播有价值的技术资料

Error from trigger function: ERROR: column does not exist

I created a trigger:

CREATE OR REPLACE FUNCTION public.delete_user() 
 RETURNS TRIGGER AS $func$
BEGIN
    EXECUTE format('UPDATE public.user SET %I = %L WHERE state = %L', 
    "state", "active", "deleted");
END;
$func$ LANGUAGE plpgsql;

 CREATE TRIGGER deleted_user
    AFTER UPDATE ON public.user
      FOR EACH ROW
      WHEN (OLD.state IS DISTINCT FROM NEW.state)
      EXECUTE PROCEDURE delete_user();

Than after running:

update public.user set state = 'active' where fullname = '1234'

I get error:

> ERROR:  column "state" does not exist
> LINE 1: ...UPDATE public.user SET %I = %L WHERE state = %L', "state", "...
>                                                              ^
> QUERY:  SELECT format('UPDATE public.user SET %I = %L WHERE state = %L', "state", "active", "deleted")
> CONTEXT:  PL/pgSQL function delete_user() line 3 at EXECUTE SQL state: 42703

This is how state defined in user table:

state text COLLATE pg_catalog."default" NOT NULL DEFAULT 'active'::text,

I'm new to Postgres so I guess it's a syntax error?

1个回答

    最佳答案
  1. This would work:

    CREATE OR REPLACE FUNCTION public.delete_user() 
      RETURNS TRIGGER AS
    $func$
    BEGIN
      EXECUTE format('UPDATE public.user SET %I = %L WHERE state = %L', 
      'state', 'active', 'deleted');
    
      RETURN NULL;  -- or OLD (for AFTER trigger)
    END
    $func$ LANGUAGE plpgsql;
    

    Single quotes for string literals instead of double quotes (for identifiers). You don't need dynamic SQL for this and can simplify:

    CREATE OR REPLACE FUNCTION public.delete_user() 
      RETURNS TRIGGER AS
    $func$
    BEGIN
      UPDATE public.user SET state = 'active' WHERE state = 'deleted';
    
      RETURN NULL;  -- or OLD (for AFTER trigger)
    END
    $func$  LANGUAGE plpgsql;
    

    While the syntax works now, it results in an endless loop and makes no sense as is. I am not sure where you are going with this.

    Related:

    Aside: public.user works since the schema-qualification removes ambiguity, but the bare table name user always requires double-quoting: "user". Never use reserved words like user as identifier.