Thread: checking for existence of a table in plpgsql.

hi,

here i use postgresql 7.2.

how can one know that a particular table exists or not in plpgsql?

need is something like this.. 
from plpgsql, i wish to insert a record in a table (create and insert,
if not exists).

---

create function ftest()
returns int as 'declare

begin
 if <table exists> then   insert into <table> (...) values (...); else   create <table> (...);   insert into <table>
(...)values (...); end if;  return row_count;
 
end;'
language 'plpgsql';

----

hope to get some lights.

Regards,
Bhuvaneswaran.

On Thu, 2002-03-14 at 06:40, Bhuvan A wrote:
> how can one know that a particular table exists or not in plpgsql?
How about :

SELECT INTO cnt count( * ) FROM pg_class WHERE relname='mytable';
IF FOUND THEN ...
END IF;

or 

IF cnt > 0 THEN ...
END IF;

This plpgsql snip, will find the number of classes that have the
"mytable" name.

Would this do the trick ? 

/BL

On Thu, Mar 14, 2002 at 11:10:35AM +0530, Bhuvan A wrote:
> 
> hi,
> 
> here i use postgresql 7.2.
> 
> how can one know that a particular table exists or not in plpgsql?
> 
> need is something like this.. 
> from plpgsql, i wish to insert a record in a table (create and insert,
> if not exists).

Besides what was already pointed out, I remember seeing some function to
check for existance of several things in the Cookbook:
http://www.brasileiro.net/postgres/cookbook/

-Roberto

-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net/      http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    
 
Once upon a time there was 3 little pigs, P1, P2 and P3