Error from trigger function: ERROR: column does not exist

Question

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?

When I delete public. I get ERROR: syntax error at or near "user" LINE 1: update user set state = 'active' where fullname = '1234' ^ SQL state: 42601 Character: 8 — Felix, Jun 03 '19 at 10:48
Why do you want to update **all** rows in the table if the triggering statement only updates a single row? — , Jun 03 '19 at 23:41

Erwin Brandstetter · Accepted Answer · 2019-06-03T22:58:29.383

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;
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.

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.

OK, great. now I have this ERROR: control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function delete_user() SQL statement "UPDATE public.user SET state = 'active' WHERE state = 'deleted'" PL/pgSQL function delete_user() line 5 at EXECUTE SQL state: 2F005 — Felix, Jun 03 '19 at 12:27

Error from trigger function: ERROR: column does not exist

1 Answers1