General advice
You mentioned it yourself, you just started using Postgres. Yet, you're tackling extremely advanced tasks right away, juggling system catalogs and operating with advanced dynamic SQL to automate things.
While your objectives seem reasonable, you still need to start at the basics. There is just too much to explain here. Start with (relevant parts of) the excellent manual. I provided a couple of deep links further down.
Answer
To get name(s) (and data type(s)) of columns involved in the primary key, rather use this simpler query:
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = 'tbl'::regclass
AND i.indisprimary;
I updated the Postgres Wiki page where your original query seems to originate from.
However, this can return multiple rows, while you only assign a single value. Assuming you have established that we are dealing with a serial
type (single column) primary key. Else use similar techniques as laid out in my previous answer to make sure.
Then use the dedicated system information function pg_get_serial_sequence()
to determine the name of the used sequence, like demonstrated in my previous answer.
Per documentation:
get name of the sequence that a serial
, smallserial
or bigserial
column uses
DO
$do$
BEGIN
EXECUTE (
SELECT format($$SELECT setval('%s'::regclass, max(%I)) FROM %s$$
, pg_get_serial_sequence(a.attrelid::regclass::text, a.attname)
, a.attname
, a.attrelid::regclass
)
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
AND a.attnum = i.indkey[0]
WHERE i.indrelid = 'tbl'::regclass
AND i.indisprimary
);
END
$do$ LANGUAGE plpgsql;
This builds and executes a query of the form:
SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;
Explain / Advise
This is advanced stuff and not really suitable for beginners. Messing with system catalogs can go south quickly if you don't know exactly what you are doing.
Stick to legal all-lower case identifiers in Postgres and plpgsql to make your life easier. But never rely on it in dynamic SQL, where you also need to defend against SQL injection at all times.
Making heavy use of format()
to build the query string conveniently & safely.
Inside a plpgsql function you cannot call SELECT
without assigning the result. You would use PERFORM
instead. Details in the manual.
I removed that completely, since I reduced everything to a single EXECUTE
.
Since the whole operation only makes sense for a single-column primary key, I simplified the JOIN
condition to a.attnum = i.indkey[0]
Note that pg_index.indkey
has the special (internal) type int2vector
. Unlike Postgres arrays its index starts with 0, not 1.
Make it a habbit to use dollar-quotes with a token around plpgsql code (including DO
statements). This allows to nest simple dollar-quotes like I do in my example. Details:
You only need a single SELECT
here:
SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;
instead of:
SELECT setval('tbl_tbl_id_seq'::regclass, (SELECT max(tbl_id) FROM tbl));
SQL Fiddle demonstrating a few things.