Обсуждение: 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