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