Re: Re: Re: Test for existence of Table

Gregory Wood wrote:
>
> What would be nice is if there were a way to only DROP a table if it exists.
> But I would consider this to be rather low priority.

This might help...

CREATE FUNCTION table_exists(TEXT) RETURNS BOOLEAN AS
'DECLARE
        tablename ALIAS FOR $1;
        temp RECORD;
BEGIN
    SELECT INTO temp *
    FROM pg_class c
    WHERE c.relname = tablename
      AND c.relkind = ''r'';

    if found then
        return ''t''::BOOLEAN;
    else
        return ''f''::BOOLEAN;
    end if;
END;'
LANGUAGE 'plpgsql';

-- test table
CREATE TABLE realtable (id INTEGER);

-- test example
SELECT table_exists('realtable'::TEXT);
SELECT table_exists('faketable'::TEXT);

-- clean up
DROP TABLE realtable;
DROP FUNCTION table_exists(TEXT);

It'd be even nicer if you could drop the table from within the PL/pgSQL
function, but I found that does not work in 7.0.0.