Thread: Dynamically create scheme

Hi,

I am writing an installscript and I would like to create a schema
dynamically, based on a new input.

-- this is the trigger that calls the install script
CREATE TRIGGER tr_install_account AFTER INSERT
 ON accounts FOR EACH ROW
 EXECUTE PROCEDURE install();

-- the install script
1 CREATE OR REPLACE FUNCTION install() RETURNS TRIGGER
2 AS '
3  BEGIN
4   INSERT INTO bedrijven (kvk, account) VALUES (''00000000'', NEW.account);
5   INSERT INTO settings (account, logo) VALUES (NEW.account, '''');

6   CREATE SCHEMA NEW.account;
7   RETURN NEW;
8  END;
9 ' LANGUAGE 'plpgsql';

It goes wrong at line 9. I want to create a schema based on the accountname
supplied. But i cant get it to work. Does anybody know how i get it to work
or what reference i must read?

Regards,

Mick vd Most v Spijk
--
'It never hurts to help' -- Eek the Cat

I think you need to create a string variable on the function and use
EXECUTE.

---------------------------------------------------------------------------

Mick van der Most van Spijk wrote:
> Hi,
>
> I am writing an installscript and I would like to create a schema
> dynamically, based on a new input.
>
> -- this is the trigger that calls the install script
> CREATE TRIGGER tr_install_account AFTER INSERT
>  ON accounts FOR EACH ROW
>  EXECUTE PROCEDURE install();
>
> -- the install script
> 1 CREATE OR REPLACE FUNCTION install() RETURNS TRIGGER
> 2 AS '
> 3  BEGIN
> 4   INSERT INTO bedrijven (kvk, account) VALUES (''00000000'', NEW.account);
> 5   INSERT INTO settings (account, logo) VALUES (NEW.account, '''');
>
> 6   CREATE SCHEMA NEW.account;
> 7   RETURN NEW;
> 8  END;
> 9 ' LANGUAGE 'plpgsql';
>
> It goes wrong at line 9. I want to create a schema based on the accountname
> supplied. But i cant get it to work. Does anybody know how i get it to work
> or what reference i must read?
>
> Regards,
>
> Mick vd Most v Spijk
> --
> 'It never hurts to help' -- Eek the Cat
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073