Re: plpgsql; execute query inside exists

On Mon, Oct 17, 2011 at 2:32 AM,  <jozsef.kurucz@invitel.hu> 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