Execute select statement IF table exists

Question

FINAL PURPOSE: Generate a query that, if a table exists, execute a statement for that table

I'm trying to execute a PSQL (9.6) statement only if a certain table exists in the schema, but it always responds with a syntax error each time I try to use conditional IF.

My query is something like...

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'users') THEN
  SELECT * FROM users;
END IF;

And the output is...

ERROR:  syntax error at or near "IF"
LINE 1: IF EXISTS(SELECT 1 FROM information_schema.tables WHERE tabl...
        ^

There is no more code than this. All alternatives that I tried failed.

Answer 1

You could (and likely have to) wrap that in a function.

CREATE FUNCTION select_if_exists
                ()
RETURNS TABLE (id integer,
               foo text)
AS
$$
BEGIN
  IF EXISTS(SELECT *
                   FROM information_schema.tables
                   WHERE table_schema = current_schema()
                         AND table_name = 'elbat') THEN
    RETURN QUERY SELECT elbat.id,
                        elbat.foo
                        FROM elbat;
  END IF;
END;
$$
LANGUAGE plpgsql;

If you call

SELECT *
       FROM select_if_exists();

when the table does not exist you get the empty set.

Create the table, call it again and you'll get the contents of the table.

CREATE TABLE elbat
             AS SELECT 1::integer id,
                       'Hello World!'::text foo;

SELECT *
       FROM select_if_exists();

But you cannot distinguish from outside, just by calling the function, if you got an empty set because the table doesn't exist or because it is empty. You'd have to RAISE an error then (But if you want that you could have just used a normal SELECT that croaks anyway, if the target table doesn't exist.)

And the table has to have the expected columns. Otherwise the SELECT in the function fails.

Notice: If you actually don't want to return a query result but execute a DML (or a DDL should work too) you could also put it in an anonymous block without defining a function. Example:

DO
$$
BEGIN
  IF EXISTS(SELECT *
                   FROM information_schema.tables
                   WHERE table_schema = current_schema()
                         AND table_name = 'elbat') THEN
    DELETE FROM elbat;
  END IF;
END;
$$
LANGUAGE plpgsql;