2011/4/19 JÃlio Almeida <julio.augusto.almeida@xxxxxxxxx>
> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
