On Mon, Oct 17, 2011 at 8:20 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> 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;
oops.. meant to say IF NOT FOUND... :-).
merlin
