Postgres: next_rowid implement

RobertMueller2 — Sat, 11 Dec 2021 13:33:33 GMT

Related to my question here (postgres nonversioned editing - sequence vs next_rowid ) on returning the next_rowid for non-versioned Postgres database (Postgres 10.6/PostGIS 2.2), I'm trying to implement the next_row id (Next_RowID—Help | ArcGIS Desktop ) with an insert trigger, however I'm buffaloed on how to get this done. Hoping someone has done this before.

When I run a basic next_rowid select in PgAdmin, I get response I'm looking for (where 'data' is the schema and 'newtest_point2' is the table)

SQL:

select sde.next_rowid('data', 'newtest_point2')‍

Return: 481

Postgres documentation (Creating PostgreSQL Trigger Example )

Function:

CREATE OR REPLACE FUNCTION test_trigger_func()
 RETURNS trigger AS
$BODY$
BEGIN
   #insert into data.newtest_point2 (objectid)
   #Values (sde.next_rowid('data', 'newtest_point2'));
   new.objectid= sde.next_rowid('data', 'newtest_point2')
 
 RETURN NEW;
END;
$BODY$
 
LANGUAGE plpgsql‍‍‍‍‍‍‍‍‍‍‍‍‍

Trigger:

CREATE TRIGGER test_trigger
BEFORE Insert
    ON newtest_point2
    FOR EACH ROW
    EXECUTE PROCEDURE test_trigger_func();‍‍‍‍‍

However my error (when adding creating points in ArcPro) :

I found a somewhat similar post on another forum ( postgresql - PostGIS Trigger Function -- max stack depth reached - Stack Overflow ,) but haven't figured out the solution.

Anyone have any suggestions or run into this before with implementing a insert trigger on next_rowid?

Note - I thought modifying back the trigger to "update" rather than "insert" did the trick, but it only appeared to work with an existing sequence.

Re: Postgres: next_rowid implement

RobertMueller2 — Mon, 30 Sep 2019 19:38:00 GMT

Note: This was incorrectly marked as answered

Re: Postgres: next_rowid implement

RobertMueller2 — Wed, 02 Oct 2019 13:49:38 GMT

Figured it out - for all those that may need this solution in the future (also fixed in the original)

Replace these 2 lines:

insert into data.newtest_point2 (objectid)
values (sde.next_rowid('data', 'newtest_point2'));

With:

new.objectid= sde.next_rowid('data', 'newtest_point2')

Check with insert (in PgAdmin for Postgres)

insert into data.newtest_point2 (shape)
values ('ST_GeomFromText('POINT(-71.060316 48.432044)', 4326))

topic Re: Postgres: next_rowid implement in ArcGIS Enterprise Questions

Postgres: next_rowid implement

Re: Postgres: next_rowid implement

Re: Postgres: next_rowid implement