AnsweredAssumed Answered

Postgres: next_rowid implement

Question asked by rmuellerjr03@gmail.com on Sep 15, 2019
Latest reply on Oct 2, 2019 by rmuellerjr03@gmail.com

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.

Outcomes