Re: Reliably finding temporary table

On Tue, Jun 07, 2005 at 04:01:02PM -0700, Ian Burrell wrote:
>
> CREATE OR REPLACE FUNCTION test_date_time_exists() RETURNS BOOLEAN AS '
> BEGIN
> RETURN EXISTS (
> SELECT * FROM pg_class
> WHERE relname = ''test_date_time''
> AND pg_table_is_visible(oid)
> );
> END;
> ' LANGUAGE 'plpgsql' STABLE
>
> However, this has been failing occassionally with errors like
>
> ERROR: cache lookup failed for relation 3454264547

The following message suggests using CASE and has_schema_privilege()
along with pg_table_is_visible():

http://archives.postgresql.org/pgsql-hackers/2005-06/msg00319.php

If you're using PostgreSQL 8.0 then I'd expect an exception handler
to work:

CREATE FUNCTION create_test_date_time() RETURNS void AS $$
BEGIN
BEGIN
CREATE TEMPORARY TABLE test_date_time (...);
EXCEPTION
WHEN DUPLICATE_TABLE THEN
NULL;
END;

RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/