Re: Create table if not exists ... how ??

Thanks guys.

Joe, I tried ( and learned! ) from your syntax. I didn't have pgsql language
installed but I googled it and figured that part out.

There was an issue with using your way though, you see the constraints
relation also needs to be considered, as if a constraint key already exist,
for any other table, not neccessary for hte table we are creating, then we
are going to get an error, which won't be covered by the count.

So I finally tried Scott's way because it will catch an exception, and I
believe the constraint key exception is included in there. Although I am not
sure, because he is catching a duplicate_table exception ? What is the most
generic exception in postgres ? Throwable in Java ?
http://www.postgresql.org/docs/8.1/interactive/errcodes-appendix.html

create or replace function create_table_if_not_exists (create_sql text)
returns bool as $$
BEGIN
BEGIN
EXECUTE create_sql;

Exception when duplicate_table THEN
RETURN false;
END;
RETURN true;

END;
$$
Language plpgsql;

SELECT create_table_if_not_exists ('CREATE TABLE post_codes
(
area character varying(10) NOT NULL,
district character varying(10) NOT NULL,
sector character varying(10) NOT NULL,
CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector)
)
WITH (
OIDS=FALSE
);
ALTER TABLE post_codes OWNER TO postgres;')

Thank you all, Jen