Postgres: next_rowid implement

1019
2
Jump to solution
09-15-2019 10:58 AM
RobertMueller2
New Contributor II

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.
0 Kudos
1 Solution

Accepted Solutions
RobertMueller2
New Contributor II

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))

View solution in original post

2 Replies
RobertMueller2
New Contributor II

Note: This was incorrectly marked as answered

0 Kudos
RobertMueller2
New Contributor II

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))