pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
checking for existence of a table in plpgsql.
3+ messages / 3 participants
[nested] [flat]

* checking for existence of a table in plpgsql.
@ 2002-03-14 05:40  Bhuvan A <bhuvansql@yahoo.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Bhuvan A @ 2002-03-14 05:40 UTC (permalink / raw)
  To: pgsql-sql


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.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: checking for existence of a table in plpgsql.
@ 2002-03-14 08:06  Bo Lorentsen <bl@netgroup.dk>
  parent: Bhuvan A <bhuvansql@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Bo Lorentsen @ 2002-03-14 08:06 UTC (permalink / raw)
  To: Bhuvan A <bhuvansql@yahoo.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: checking for existence of a table in plpgsql.
@ 2002-03-14 16:28  Roberto Mello <rmello@cc.usu.edu>
  parent: Bhuvan A <bhuvansql@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Roberto Mello @ 2002-03-14 16:28 UTC (permalink / raw)
  To: Bhuvan A <bhuvansql@yahoo.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-03-14 16:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-14 05:40 checking for existence of a table in plpgsql. Bhuvan A <bhuvansql@yahoo.com>
2002-03-14 08:06 ` Bo Lorentsen <bl@netgroup.dk>
2002-03-14 16:28 ` Roberto Mello <rmello@cc.usu.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox