Re: plpgsql; execute query inside exists

Hi,

Thanks for the reply!
But I don't want to check if the table exists, I want to see the
result of the SELECT query, if a row presence or not.
The tmp_tbl is a dynamic generated table name, but when I write the
code without EXECUTE, I get syntax error too.
In this case how can I check if a SELECT has result or not?

SELECT INTO rndmd5 md5(random()::text);

tmp_tbl := 'tbl_tmp_' || rndmd5;

IF NOT EXISTS(SELECT * FROM tmp_tbl)
THEN
END IF;

ERROR: syntax error at or near "$1"
LINE 1: SELECT NOT EXISTS(SELECT * FROM $1 )

Thanks!

2011/10/17 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>