On Mon, Oct 17, 2011 at 8:20 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > 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; oops.. meant to say IF NOT FOUND... :-). merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
