1

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 "("

4
  • 1
    Try (new.state).last_active. Nov 26 '21 at 17:06
  • @LaurenzAlbe I did, even though it works in queries, it gives me syntax error. See the last sentence
    – bca
    Nov 26 '21 at 17:20
  • 1
    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? Nov 28 '21 at 20:43
  • @LaurenzAlbe 12. Last snippet works, first one gives me an error.
    – bca
    Nov 29 '21 at 21:05
2

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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.