Trying to convert simple execution stored procedure to postgres function - can't get CURRVAL('table_seq') to function

Question

The MySQL Stored Procedure was:

     BEGIN
     set @sql=_sql;
     PREPARE stmt FROM @sql; 
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     set _ires=LAST_INSERT_ID();
     END$$

I tried to convert it to:

     BEGIN  
     EXECUTE _sql;
     SELECT INTO _ires CURRVAL('table_seq');
     RETURN;
     END;

I get the error:

SQL error:

ERROR:  relation "table_seq" does not exist
LINE 1: SELECT CURRVAL('table_seq')
                       ^
QUERY:  SELECT CURRVAL('table_seq')
CONTEXT:  PL/pgSQL function "myexecins" line 4 at SQL statement
In statement:
SELECT myexecins('SELECT * FROM tblbilldate WHERE billid = 2')

The query used is for testing purposes only. I believe this function is used to get the row id of the inserted or created row from the query. Any Suggestions?

intgr · Accepted Answer · 2011-01-07 08:44:51Z

2

When you create tables with serial columns, sequences are, by default, named as tablename_columnname_seq, but it seems that you're trying to access tablename_seq. So with a table called foobar and primary key column foobar_id it ends up being foobar_foobar_id_seq.

By the way, a cleaner way to get the primary key after an insert is using the RETURNING clause in INSERT. E.g.:

_sql = 'INSERT INTO sometable (foobar) VALUES (123) RETURNING sometable_id';
EXECUTE _sql INTO _ires;

edited Jan 7, 2011 at 8:44

answered Jan 7, 2011 at 8:33

intgr

20.7k6 gold badges63 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

araqnid Over a year ago

you can use the pg_get_serial_sequence function to avoid having to deal with the naming convention of the sequence

Joshua Riddle Over a year ago

yes... but does pg_get_serial_sequence() work if you do not know the sql in the _sql variable?

mu is too short · Accepted Answer · 2011-01-07 07:59:03Z

2

PostgreSQL is saying that there is no sequence called "table_seq". Are you sure that that is the right name? The name you would use would depend on what is in _sql as each SERIAL or BIGSERIAL gets its own sequence, you can also define sequences and wire them up by hand.

In any case, lastval() is a closer match to MySQL's LAST_INSERT_ID(), lastval() returns the most recently returned value from any sequence in the current session:

lastval
Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used by nextval in the current session. It is an error to call lastval if nextval has not yet been called in the current session.

Using lastval() also means that you don't have to worry about what's in _sql, unless of course it doesn't use a sequence at all.

answered Jan 7, 2011 at 7:59

mu is too short

436k71 gold badges863 silver badges822 bronze badges

1 Comment

Joshua Riddle Over a year ago

Perfect..because i don't want to go back and find everytime this function is being called and tell it to send the table name and column as a parameter along with the query.

Collectives™ on Stack Overflow

Trying to convert simple execution stored procedure to postgres function - can't get CURRVAL('table_seq') to function

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related