Re: plpgsql; execute query inside exists

On 18 October 2011 09:57, <jozsef(dot)kurucz(at)invitel(dot)hu> wrote:
> 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.

So you want to check that the table contains data? In that case it
makes no sense to create the table if it doesn't contain data. It may
very well exist already.

> The tmp_tbl is a dynamic generated table name, but when I write the
> code without EXECUTE, I get syntax error too.

They were explaining why you got the error, they were not telling you
to leave out EXECUTE for dynamic SQL.

> 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;

You really should at least limit the possible amount of results from
that SELECT statement. You're not interested in the results.

Anyway, the way to do this in plpgsql is:

EXECUTE SELECT * FROM tmp_tbl LIMIT 1;
IF NOT FOUND THEN
...
END IF;

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.