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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Matt,

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org