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
