By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE OR REPLACE FUNCTION sequence_currval(_tbl text, _col text, OUT currval bigint)
LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence(_tbl, _col)
INTO currval;
END
$func$;
CREATE FUNCTION
CREATE TABLE foo (id serial);
CREATE TABLE
-- Our custom function never fails:
SELECT pg_get_serial_sequence('foo', 'id') AS seq_name, sequence_currval('foo', 'id');
| seq_name | sequence_currval |
|---|---|
| public.foo_id_seq | 0 |
SELECT 1
-- Must fail because nextval() has not been called in this session!
SELECT currval(pg_get_serial_sequence('foo', 'id'));
ERROR: currval of sequence "foo_id_seq" is not yet defined in this session
-- Same here: must fail!
SELECT lastval()
ERROR: lastval is not yet defined in this session
-- Using default sequence name as table name (as shown above)
SELECT * FROM public.foo_id_seq;
| last_value | log_cnt | is_called |
|---|---|---|
| 1 | 0 | f |
SELECT 1
INSERT INTO foo DEFAULT VALUES;
INSERT 0 1
-- now, these work, too:
SELECT lastval(), currval(pg_get_serial_sequence('foo', 'id'));
| lastval | currval |
|---|---|
| 1 | 1 |
SELECT 1
-- Note how "is_called" changed:
SELECT * FROM public.foo_id_seq;
| last_value | log_cnt | is_called |
|---|---|---|
| 1 | 32 | t |
SELECT 1
SELECT sequence_currval('foo', 'id');
| sequence_currval |
|---|
| 1 |
SELECT 1
INSERT INTO foo DEFAULT VALUES;
INSERT 0 1
SELECT lastval(), currval(pg_get_serial_sequence('foo', 'id'));
| lastval | currval |
|---|---|
| 2 | 2 |
SELECT 1
SELECT sequence_currval('foo', 'id');
| sequence_currval |
|---|
| 2 |
SELECT 1
-- Note how is_called changed
SELECT * FROM public.foo_id_seq;
| last_value | log_cnt | is_called |
|---|---|---|
| 2 | 31 | t |
SELECT 1