Some of your problem here comes from using an old-style SQL function.
The string body of such a function is opaque to pg_dump, so it has
no way to know about the dependency on my_seq. You could make it
new-style (SQL spec compliant) instead:
regression=# create sequence my_seq;
CREATE SEQUENCE
regression=# create function gen_id() returns character varying
regression-# begin atomic select 'PREFIX_'||nextval('public.my_seq'::regclass)::VARCHAR;
regression-# end;
CREATE FUNCTION
Now the dependency is known:
regression=# select pg_describe_object(classid,objid,objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid)as ref, deptype from pg_depend where objid >= 'my_seq'::regclass;
obj | ref | deptype
-------------------+-----------------+---------
function gen_id() | schema public | n
function gen_id() | sequence my_seq | n
sequence my_seq | schema public | n
(3 rows)
and pg_dump will honor it.
But as David said, using a volatile function in a GENERATED
expression is unsupported and is not going to work well.
You would probably be better off filling the column in a
BEFORE INSERT trigger.
regards, tom lane
