Check if sequence exists in Postgres (plpgsql)

postgresql stored-procedures plpgsql
Ilia Choly picture Ilia Choly · Aug 10, 2012 · Viewed 29.6k times · Source

I'm trying to test, within a stored procedure, whether a sequence already exists.

IF EXISTS SEQUENCE seq_name
    RAISE EXCEPTION 'sequence % already exists!', seq_name
END IF;

I have tried several variations of the snippet above without luck. I must be giving Google the wrong terms because I can't seem to find anything on the topic. Any help is appreciated!

Answer

rfusca picture rfusca · Aug 10, 2012

You should be able query the pg_class table to see if the relname exists.

IF EXISTS (SELECT 0 FROM pg_class where relname = '<my sequence name here>' )
THEN
  --stuff here
END IF;

Related questions

Store query result in a variable using in PL/pgSQL

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL? I have a function: CREATE OR REPLACE FUNCTION test(x numeric) RETURNS character varying AS $BODY$ DECLARE name character varying(255); begin …

Read More
Postgres FOR LOOP

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the …

Read More
Return multiple fields as a record in PostgreSQL with PL/pgSQL

I am writing a SP, using PL/pgSQL. I want to return a record, comprised of fields from several different tables. Could look something like this: CREATE OR REPLACE FUNCTION get_object_fields(name text) RETURNS RECORD AS $$ BEGIN -- …

Read More