Re: Check if table exists

All,

I am doing something similar putting all the field (column) names into
data objects in a class::method. Here is the select for Postgresql I am
using:

SELECT a.attname as \"column\", pg_catalog.format_type(a.atttypid,
a.atttypmod) as \"datatype\"
FROM pg_catalog.pg_attribute a
WHERE a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(TABLE_NAME_HERE)$'
AND pg_catalog.pg_table_is_visible(c.oid)
)

Hope this helps.
Kulmacet

>
> Hi,
>
> I'm a novice also, but I'm sure that one way of accomplishing this is to
> check the metadata table/views (eg. information_schema.tables).
>
> If you want to make it a little cleaner, you could always wrap the check
> fo the meta into a function that returns a bool.
>
> HTH,
>
> David
>
>
> --- On Wed, 3/18/09, Leif B. Kristensen <leif(at)solumslekt(dot)org> wrote:
>
>> From: Leif B. Kristensen <leif(at)solumslekt(dot)org>
>> Subject: [NOVICE] Check if table exists
>> To: pgsql-novice(at)postgresql(dot)org
>> Date: Wednesday, March 18, 2009, 11:55 AM
>> Even if I've been using Postgres for some years, I
>> figure that this
>> question fits best on this list:
>>
>> How do I check if a table exists, eg. from a PLPGSQL
>> function?
>>
>> I've got a section of a function that only should be
>> run if it's called
>> from a certain context, that is if the temporary table
>> 'tmp_sources' is
>> found. But if I try with the line
>>
>> IF EXISTS (SELECT * FROM tmp_sources) THEN
>>
>> The function throws an error:
>>
>> ERROR: relation "tmp_sources" does not exist
>>
>> Which is kind of off the mark in my context.
>> --
>> Leif Biberg Kristensen | Registered Linux User #338009
>> Me And My Database: http://solumslekt.org/blog/
>>
>> -
>> Sent via pgsql-novice mailing list
>> (pgsql-novice(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>
> -
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>