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