3. Or even create a pg_get_sequence() function: SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
Actually, this is the best solution :)
OK, attached is a pg_get_serial_sequence(schema, table, column) function . I have tested it with crazy names and it seems to be good. It works like this:
SELECT setval(pg_get_serial_sequence('public', 'mytable', 'mycol'), 1, false);
If someone approves it, i'll work on making it a built-in backend function, and make pg_dump use it.
This will also be great for our app, since we would no longer have to have hard-coded sequence names in our code. (For getting last sequence val on oid-less tables)
Chris
CREATE FUNCTION pg_get_serial_sequence(name, name, name) RETURNS text
AS '
SELECT
pg_catalog.quote_ident(pn_seq.nspname) || ''.'' ||
pg_catalog.quote_ident(seq.relname)
FROM
pg_catalog.pg_namespace pn,
pg_catalog.pg_class pc,
pg_catalog.pg_attribute pa,
pg_catalog.pg_depend pd,
pg_catalog.pg_class seq,
pg_catalog.pg_namespace pn_seq
WHERE
pn.nspname=$1
AND pc.relname=$2
AND pa.attname=$3
AND pn.oid=pc.relnamespace
AND pc.oid=pa.attrelid
AND pd.objid=seq.oid
AND pd.classid=seq.tableoid
AND pd.refclassid=seq.tableoid
AND pd.refobjid=pc.oid
AND pd.refobjsubid=pa.attnum
AND pd.deptype=''i''
AND seq.relkind=''S''
AND seq.relnamespace=pn_seq.oid
'
LANGUAGE sql;---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
