Обсуждение: check table existence...
Dear list,
I would like to create a function which gets a tablename and checks if the specific table exists.The return value should be a bool.
Now I'm wondering how to do this the best way.
Any suggestions?
kind regards and thanks in advance,
Moritz
am Mon, dem 15.01.2007, um 13:18:11 +0100 mailte Moritz Bayer folgendes: > Dear list, > > I would like to create a function which gets a tablename and checks if the > specific table exists.The return value should be a bool. > Now I'm wondering how to do this the best way. > > Any suggestions? You can ask pg_tables: select count(1) from pg_tables where tablename = 'foo' and schemaname = 'public'; This ask for a table called 'foo' in the schema 'public'. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer wrote: > am Mon, dem 15.01.2007, um 13:18:11 +0100 mailte Moritz Bayer folgendes: >> Dear list, >> >> I would like to create a function which gets a tablename and checks if the >> specific table exists.The return value should be a bool. >> Now I'm wondering how to do this the best way. >> >> Any suggestions? > > You can ask pg_tables: > > select count(1) from pg_tables where tablename = 'foo' and schemaname = 'public'; > > > This ask for a table called 'foo' in the schema 'public'. If you do (something like) that in pl/pgsql, you could RETURN FOUND after performing that query. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
Something like this will help you......
=======================
CREATE OR REPLACE FUNCTION public.check_table(varchar, varchar)
RETURNS boolean AS $$
DECLARE
v_cnt integer;
v_tbl boolean;
BEGIN
SELECT count(1) INTO v_cnt FROM pg_tables where tablename = $1 and schemaname = $2;
IF v_cnt > 0 THEN
v_tbl = 'true';
END IF;
IF v_cnt = 0 THEN
v_tbl = 'false';
END IF;
return v_tbl;
END;
$$ LANGUAGE 'plpgsql'
=========================
select check_table('emp', 'public');
-----------------------
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
=======================
CREATE OR REPLACE FUNCTION public.check_table(varchar, varchar)
RETURNS boolean AS $$
DECLARE
v_cnt integer;
v_tbl boolean;
BEGIN
SELECT count(1) INTO v_cnt FROM pg_tables where tablename = $1 and schemaname = $2;
IF v_cnt > 0 THEN
v_tbl = 'true';
END IF;
IF v_cnt = 0 THEN
v_tbl = 'false';
END IF;
return v_tbl;
END;
$$ LANGUAGE 'plpgsql'
=========================
select check_table('emp', 'public');
-----------------------
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
On 1/15/07, Alban Hertroys <alban@magproductions.nl > wrote:
A. Kretschmer wrote:
> am Mon, dem 15.01.2007, um 13:18:11 +0100 mailte Moritz Bayer folgendes:
>> Dear list,
>>
>> I would like to create a function which gets a tablename and checks if the
>> specific table exists.The return value should be a bool.
>> Now I'm wondering how to do this the best way.
>>
>> Any suggestions?
>
> You can ask pg_tables:
>
> select count(1) from pg_tables where tablename = 'foo' and schemaname = 'public';
>
>
> This ask for a table called 'foo' in the schema 'public'.
If you do (something like) that in pl/pgsql, you could RETURN FOUND
after performing that query.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Thanks, that's exactly what I was looking for :-)
kind regards,
Morirt
2007/1/15, A. Kretschmer <andreas.kretschmer@schollglas.com>:
am Mon, dem 15.01.2007, um 13:18:11 +0100 mailte Moritz Bayer folgendes:
> Dear list,
>
> I would like to create a function which gets a tablename and checks if the
> specific table exists.The return value should be a bool.
> Now I'm wondering how to do this the best way.
>
> Any suggestions?
You can ask pg_tables:
select count(1) from pg_tables where tablename = 'foo' and schemaname = 'public';
This ask for a table called 'foo' in the schema 'public'.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Shoaib Mir wrote: > Something like this will help you...... Or shorter: > ======================= > > CREATE OR REPLACE FUNCTION public.check_table(varchar, varchar) > RETURNS boolean AS $$ > DECLARE > v_cnt integer; > v_tbl boolean; > BEGIN PERFORM 1 FROM pg_tables where tablename = $1 and > schemaname = $2; RETURN FOUND; > END; > $$ LANGUAGE 'plpgsql' I'm pretty sure that should work. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //