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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

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)

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 //

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend