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