On Mon, Oct 17, 2011 at 2:32 AM, <jozsef.kurucz@xxxxxxxxxx> wrote: > Hi there, > > I would like to use EXISTS in a small plpgsql function but I always > get a "syntax error". How can I execute a query inside the > EXISTS function? > > > > IF NOT EXISTS(EXECUTE 'SELECT * FROM '|| tmp_tbl) > THEN > CREATE TABLE tt(); > > > > > ERROR: syntax error at or near "EXECUTE" > LINE 1: SELECT NOT EXISTS(EXECUTE 'SELECT * FROM '|| $1 ) EXECUTE is a top level statement -- you can't run it inside a query like that. Also, EXISTS is not a way to check to see if a table does exist -- it is a clause for the presence of a row and returns true if it finds one -- but if the table does not exist you would get an SQL error. A better way to do this is to query information_schema: PERFORM 1 FROM information_schema.tables where schema_name = x and table_name = y; IF FOUND THEN CREATE TABLE ... END IF; (there is a race condition in the above code -- do you see it? if concurrent access to this function is an issue, you have to LOCK an object before running the PERFORM or perhaps use an advisory lock). merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
