Re: plpgsql; execute query inside exists

On Mon, Oct 17, 2011 at 8:20 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Mon, Oct 17, 2011 at 2:32 AM,  <jozsef(dot)kurucz(at)invitel(dot)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