Thread: PL/pgSQL and new table creation
I am looking to automate and simplify the creation of new, empty tables from a template. I'm running PostgreSQL 7.3.4 under Fedora Core 1. My code: CREATE OR REPLACE FUNCTION sp_newtable(TEXT) RETURNS VOID AS ' DECLARE newtable ALIAS FOR $1; BEGIN SELECT * INTO newtable FROM d_master WHERE False; RETURN; END; ' LANGUAGE 'plpgsql'; But when I run it, I get the following error. WARNING: plpgsql: ERROR during compile of sp_newtable near line 8 ERROR: $1 is declared CONSTANT Any tips? I've hammered on this thing for awhile now... Thanks
Matt, > But when I run it, I get the following error. > > WARNING: plpgsql: ERROR during compile of sp_newtable near line 8 > ERROR: $1 is declared CONSTANT You can't use a variable in place of an object name, unless you execute the query as a dynamic string, e.g.: CREATE FUNCTION new_table(TEXT) returns INT as 'DECLARE tbname ALIAS for $1; BEGIN EXECUTE 'SELECT * INTO ' || newtable || ' FROM d_master'; RETURN 1; END;' LANGUAGE plpgsql; -- -Josh Berkus Aglio Database Solutions San Francisco