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

On 07/19/2010 10:43 AM, Jennifer Trey wrote:
> No.... I don't want to drop it ... there is valuable data in there! I
> only want to create it if it doesn't already exist... likely going to
> happen first time the application will run. I want to create the table
> then and populate. But not the next time.

Sorry -- didn't understand that from your original post. How 'bout
something like:

8<---------------------
CREATE OR REPLACE FUNCTION
conditional_create_table(schemaname text, tablename text, create_sql
text, tbl_owner text)
RETURNS text AS $$
DECLARE
tbl_cnt int;
fqtn text := schemaname || '.' || tablename;
BEGIN
SELECT COUNT(*) INTO tbl_cnt FROM information_schema.tables
WHERE table_schema= schemaname AND table_name=tablename;

IF tbl_cnt < 1 THEN
EXECUTE 'CREATE TABLE ' || fqtn || create_sql;
EXECUTE 'ALTER TABLE ' || fqtn || ' OWNER TO ' || tbl_owner;
RETURN 'CREATE';
ELSE
RETURN 'SKIP';
END IF;
END
$$ LANGUAGE plpgsql STRICT;

SELECT conditional_create_table(
'public',
'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)',
'postgres'
);
conditional_create_table
--------------------------
CREATE
(1 row)

SELECT conditional_create_table(
'public',
'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)',
'postgres'
);
conditional_create_table
--------------------------
SKIP
(1 row)

contrib_regression=# \d public.post_codes
Table "public.post_codes"
Column | Type | Modifiers
----------+-----------------------+-----------
area | character varying(10) | not null
district | character varying(10) | not null
sector | character varying(10) | not null
Indexes:
"post_codes_pkey" PRIMARY KEY, btree (area, district, sector)

8<---------------------

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support