Reset the sequence of a serial PK with dynamic SQL

Question

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this?

My goal is to do this within a DO/BEGIN block for performance reasons plus it'll help me understand plpgsql by doing something useful so I'm not going to mix this in to PHP unless I really have to.

DO $$
 DECLARE PKEY VARCHAR;
BEGIN

SELECT pg_attribute.attname INTO PKEY 
FROM pg_index, pg_class, pg_attribute 
WHERE pg_class.oid = 'parts1'::regclass 
AND indrelid = pg_class.oid 
AND pg_attribute.attrelid = pg_class.oid 
AND pg_attribute.attnum = any(pg_index.indkey) 
AND indisprimary;

--SELECT setval('parts1_id_seq', (SELECT MAX(pkey) + 1 FROM parts));
SELECT setval('parts1_' || PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

  END;
$$ LANGUAGE plpgsql;

Answer 1

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:

  • Insert varchar with single quotes in PostgreSQL

• 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.

Answer 2

Any time you have a SQL statement that it dynamic in nature (can potentially change at runtime) that you need to run inside of a function, you need to run EXECUTE for it.

See Section 40.5.4. Executing Dynamic Commands

You need to be aware that executing SQL this way CAN be vulnerable to SQL Injection attacks.

Your line above:

SELECT setval('parts1_' || INTO PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

Would become (at least, I believe this is the intent)

EXECUTE 'SELECT setval(''parts1_' || PKEY || '_seq'', (SELECT MAX(' || pkey || ') + 1 FROM parts))';

• Note .. the doubled single quote ('') inside a string is treated as a literal single quote (like \')
• Note 2 .. to protect against SQL Injection, you can EXECUTE a formatted string (as Erwin Brandstetter has already supplied several examples to you in a different post)