By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

trigger creating table

P: 7
I want to create a function that 'on update' and 'on insert' in table A column nome_tabella create another table named as the record just added in A:
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION trig_creazione_tabella() RETURNS trigger AS $$
  2.     BEGIN
  3.         IF (TG_OP = 'UPDATE') THEN
  4.     CREATE TABLE NEW.nome_tabella (id serial NOT NULL, sigla_comune character varying(4));
  5.         ELSIF (TG_OP = 'INSERT') THEN
  6.     CREATE TABLE NEW.nome_tabella (id serial NOT NULL, sigla_comune character varying(4));
  7.         END IF;
  8.     RETURN NEW;
  9.     END;
  10. $$ LANGUAGE plpgsql;
  11.  
on pgAdminIII i have an error on $1 (NEW.nome_tabella) and i can't understand what's the problem, so I humbly ask help.

Thanks to all
Sentenza
May 13 '10 #1

✓ answered by rski

You should use dynamic SQL

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION trig_creazione_tabella() RETURNS trigger AS $$
  2. BEGIN
  3. IF (TG_OP = 'UPDATE') THEN
  4. EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (id serial NOT NULL, sigla_comune character varying(4))';
  5. ELSIF (TG_OP = 'INSERT') THEN
  6. EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (id serial NOT NULL, sigla_comune character varying(4))';
  7. END IF;
  8. RETURN NEW;
  9. END;
  10. $$ LANGUAGE plpgsql;
  11.  

Share this Question
Share on Google+
2 Replies


Expert 100+
P: 700
You should use dynamic SQL

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION trig_creazione_tabella() RETURNS trigger AS $$
  2. BEGIN
  3. IF (TG_OP = 'UPDATE') THEN
  4. EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (id serial NOT NULL, sigla_comune character varying(4))';
  5. ELSIF (TG_OP = 'INSERT') THEN
  6. EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (id serial NOT NULL, sigla_comune character varying(4))';
  7. END IF;
  8. RETURN NEW;
  9. END;
  10. $$ LANGUAGE plpgsql;
  11.  
May 13 '10 #2

P: 7
perfect, now it works fine!

thank you very much!!

bye Sentenza
May 17 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.