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@xxxxxxxxx>:
> On Mon, Oct 17, 2011 at 8:20 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
>> On Mon, Oct 17, 2011 at 2:32 AM, <jozsef.kurucz@xxxxxxxxxx> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
