Postgres before insert trigger access field of a type

Question

In the DB, I have a type and a table that one of it's rows is that type, such as this:

CREATE TYPE state (last_active timestamp, ...)
CREATE TABLE devices ( state state, ... )

For any interaction from the device, I want to update last_active field in the state. I added following BEFORE UPDATE trigger:

create or replace function update_last_active_on_position_update_hook()
    returns trigger
    language plpgsql
as $$
declare
    new_state state;
begin
    if new.state IS NOT NULL then
        new.state.last_active := now();
    end if;
    RETURN new;
end
$$;

When I do that, I get the following error: [42601] ERROR: "new.state.last_active" is not a known variable

But if I declare a variable with type state, copy new.state to that variable, update the field and copy back; it works. Working version:

create or replace function update_last_active_on_position_update_hook()
    returns trigger
    language plpgsql
as $$
declare
    new_state state;
begin
    if new.state IS NOT NULL then
        new_state := new.state;
        new_state.last_active := now();
        new.state := new_state;
    end if;
    RETURN new;
end
$$;

But I don't like to do copy-update-copy. Is there another solution?

I tried to get new.state in parens as (new.state).last_active but it gives me a syntax error near "("

@LaurenzAlbe I did, even though it works in queries, it gives me syntax error. See the last sentence — bca
– bca, Commented Nov 26, 2021 at 17:20
I just tried your code on my 14.1 PostgreSLQ database, and it works like a charm. Which version are you using? Normal PostgreSQL or a fork? — Laurenz Albe
– Laurenz Albe, Commented Nov 28, 2021 at 20:43
@LaurenzAlbe 12. Last snippet works, first one gives me an error. — bca
– bca, Commented Nov 29, 2021 at 21:05

Pang · Accepted Answer · 2022-04-26 00:28:20Z

I can reproduce the problem with snippet 1 in Postgres 12:

db<>fiddle here

This is clearly a shortcoming of PL/pgSQL. But no need to report it as it has been fixed in Postgres 14. See:

db<>fiddle here

The release notes:

Improve PL/pgSQL's expression and assignment parsing (Tom Lane)

This change allows assignment to array slices and nested record fields.

In particular, Tom Lane commented:

0005 adds documentation and test cases. It also fixes a couple of pre-existing problems that the plpgsql parser had with assigning to sub-fields of record fields, which I discovered while making the tests.

Workaround

But I don't like to do copy-update-copy. Is there another solution?

Here is a workaround for Postgres 13 or earlier:

CREATE OR REPLACE FUNCTION update_last_active_on_position_update_hook()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _state state;
BEGIN
   NEW.state := json_populate_record (NEW.state, json_build_object('last_active', now()));  -- !!
   RETURN NEW;
END
$func$;

db<>fiddle here

See:

How to set value of composite variable field using dynamic SQL

Better? Faster? Not sure. A bit shorter in any case.

While being at it, I moved the condition into a WHEN clause in the trigger definition. This way, the trigger function is only even called when needed. That's cheaper:

CREATE TRIGGER foo
BEFORE UPDATE ON devices
FOR EACH ROW
WHEN (NEW.state IS NOT NULL)
EXECUTE FUNCTION update_last_active_on_position_update_hook();

See:

Is it possible to dynamically loop through a table's columns?

Stack Exchange Network

Postgres before insert trigger access field of a type

1 Answer 1

Workaround

Your Answer

Hot Network Questions

Postgres before insert trigger access field of a type

1 Answer 1

Workaround

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions