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?

Answer 1

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;