Thread: create table sintax

2011/4/19 Júlio Almeida <julio.augusto.almeida@gmail.com>
> If I run
> create table newtable (like oldtable including constraints);
> in the SQL window with works just file.
> But if i execute
> execute 'create table '||newtable||' (LIKE '||oldtable||' including constraints)';
> inside a function, in a LOOP, the constraints aren't created.
> What is the problem?

You probably missing something in your function.
Please find a simple example that shows how to create a table like
other table using function.

Remember to use *quote_ident* function when you use database
identifiers in dynamic SQL's.

BEGIN;

CREATE TABLE original (
  a integer,
  b text,
  c date,
  CONSTRAINT orginal_ck_text_has_dog CHECK (position('dog' in b) <> 0),
  CONSTRAINT orginal_ck_date_is_recent CHECK (c >= '2000-01-01'::date)
  );

insert into original(a, b, c) VALUES(1, 'hot dog', now());
/* error, as expected
insert into original(a, b, c) VALUES(2, 'hot cat', now());
*/

-- function to copy other table with constraints
CREATE OR REPLACE FUNCTION create_table_like(p_orig_table text,
p_new_table text)
RETURNS VOID AS $BODY$
BEGIN
    EXECUTE 'CREATE TABLE '||quote_ident(p_new_table)
          ||' (LIKE '||quote_ident(p_orig_table)||' including constraints)';
    RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT;

-- execute function to create a new table like old one
SELECT create_table_like('original'::text, 'copied'::text);

insert into copied(a, b, c) VALUES(1, 'hot dog', now());
/* error, as expected
insert into copied(a, b, c) VALUES(2, 'hot cat', now());
*/
-- ROLLBACK;

Hope this helps,
Taras Kopets