Обсуждение: problem with variable
Hello everyone When the trigger executes the function which I copy below: IF (TG_OP = 'UPDATE') THEN EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY CHECK (nome_tabella = '||NEW.nome_tabella||'::text)) INHERITS (database_t); first use of the variable '||NEW.nome_tabella||' is correct and the table is created with the name, for example "azienda_vallone", but I do not know why, the second use of the variable is incorrect because the CHECK insert on table is nome_tabella = azienda_vallone.*. I can not understand why postgresql insert point and asterisk at the end of the variable. if anyone can give me some suggestions on how to resolve... thank you very much Luca
"coviolo@libero.it" <coviolo@libero.it> wrote:
> When the trigger executes the function which I copy below:
> IF (TG_OP = 'UPDATE') THEN
> EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY
> CHECK (nome_tabella = '||NEW.nome_tabella||'::text))
^ ^
> INHERITS (database_t);
> first use of the variable '||NEW.nome_tabella||' is correct and the table is
> created with the name, for example "azienda_vallone", but I do not know why,
> the second use of the variable is incorrect because the CHECK insert on table
> is nome_tabella = azienda_vallone.*.
> I can not understand why postgresql insert point and asterisk at the end of
> the variable.
> if anyone can give me some suggestions on how to resolve...
You probably need to replace the last two quotes with "'''".
Tim
something like this: IF (TG_OP = 'UPDATE') THEN EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY CHECK (nome_tabella = '''||NEW.nome_tabella||'''::text)) INHERITS (database_t); 3 quotes first and 3 quotes after the second variable? tnx Luca >----Messaggio originale---- >Da: tim@tim-landscheidt.de >Data: 09/06/2010 14.02 >A: <pgsql-novice@postgresql.org> >Ogg: Re: [NOVICE] problem with variable > >"coviolo@libero.it" <coviolo@libero.it> wrote: > >> When the trigger executes the function which I copy below: > >> IF (TG_OP = 'UPDATE') THEN >> EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY >> CHECK (nome_tabella = '||NEW.nome_tabella||'::text)) > ^ ^ >> INHERITS (database_t); > >> first use of the variable '||NEW.nome_tabella||' is correct and the table is >> created with the name, for example "azienda_vallone", but I do not know why, >> the second use of the variable is incorrect because the CHECK insert on table >> is nome_tabella = azienda_vallone.*. >> I can not understand why postgresql insert point and asterisk at the end of >> the variable. >> if anyone can give me some suggestions on how to resolve... > >You probably need to replace the last two quotes with " ' ' ' ". > >Tim > > >-- >Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-novice >
Hi
On 9 June 2010 16:24, coviolo@libero.it <coviolo@libero.it> wrote:
> something like this:
>
> IF (TG_OP = 'UPDATE') THEN
> EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY
> CHECK (nome_tabella = '''||NEW.nome_tabella||'''::text))
> INHERITS (database_t);
>
> 3 quotes first and 3 quotes after the second variable?
Just a guess, but I think this is what you want:
IF (TG_OP = 'UPDATE') THEN
EXECUTE 'CREATE TABLE ' || NEW.nome_tabella || ' (ordinativo serial PRIMARY KEY
CHECK (nome_tabella = "' || NEW.nome_tabella || '"::text))
INHERITS (database_t);'
i.e. you want:
CREATE TABLE table_name (x serial PRIMARY KEY
CHECK (column_name = "table_name"::text))
INHERITS (database_t);
The first "table_name" is not quoted. The second one has
double-quotes (") around it. So you need the whole thing to be quoted
with single-quotes (') and then use '...' || variable || '...' for the
first one and '..."' || variable || '"::text...' for the second one.
I hope that makes sense.
--
Michael Wood <esiotrot@gmail.com>
On 9 June 2010 16:51, Michael Wood <esiotrot@gmail.com> wrote: > Hi > > On 9 June 2010 16:24, coviolo@libero.it <coviolo@libero.it> wrote: >> something like this: >> >> IF (TG_OP = 'UPDATE') THEN >> EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY >> CHECK (nome_tabella = '''||NEW.nome_tabella||'''::text)) >> INHERITS (database_t); >> >> 3 quotes first and 3 quotes after the second variable? > > Just a guess, but I think this is what you want: > > IF (TG_OP = 'UPDATE') THEN > EXECUTE 'CREATE TABLE ' || NEW.nome_tabella || ' (ordinativo serial PRIMARY KEY > CHECK (nome_tabella = "' || NEW.nome_tabella || '"::text)) > INHERITS (database_t);' > > i.e. you want: > > CREATE TABLE table_name (x serial PRIMARY KEY > CHECK (column_name = "table_name"::text)) > INHERITS (database_t); Sorry, I was talking nonsense. You want column_name = 'table_name' but because the ' will be inside a quoted string, you need to double it. So you were right. Use ...nome_tabella = ''' || NEW.nome_tabella || '''::text... -- Michael Wood <esiotrot@gmail.com>
tnx Tim an tnx Michael I'll try tonight...Tomorrow I hope I have therefore resolved. bye Luca >----Messaggio originale---- >Da: esiotrot@gmail.com >Data: 09/06/2010 16.54 >A: "coviolo@libero.it"<coviolo@libero.it> >Cc: <pgsql-novice@postgresql.org> >Ogg: Re: [NOVICE] problem with variable > >On 9 June 2010 16:51, Michael Wood <esiotrot@gmail.com> wrote: >> Hi >> >> On 9 June 2010 16:24, coviolo@libero.it <coviolo@libero.it> wrote: >>> something like this: >>> >>> IF (TG_OP = 'UPDATE') THEN >>> EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY >>> CHECK (nome_tabella = '''||NEW.nome_tabella||'''::text)) >>> INHERITS (database_t); >>> >>> 3 quotes first and 3 quotes after the second variable? >> >> Just a guess, but I think this is what you want: >> >> IF (TG_OP = 'UPDATE') THEN >> EXECUTE 'CREATE TABLE ' || NEW.nome_tabella || ' (ordinativo serial PRIMARY KEY >> CHECK (nome_tabella = "' || NEW.nome_tabella || '"::text)) >> INHERITS (database_t);' >> >> i.e. you want: >> >> CREATE TABLE table_name (x serial PRIMARY KEY >> CHECK (column_name = "table_name"::text)) >> INHERITS (database_t); > >Sorry, I was talking nonsense. You want column_name = 'table_name' >but because the ' will be inside a quoted string, you need to double >it. So you were right. Use ...nome_tabella = ''' || NEW.nome_tabella >|| '''::text... > >-- >Michael Wood <esiotrot@gmail.com> > >-- >Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-novice >
ok the latest version works perfectly. Many thanks to all Ciao Luca >----Messaggio originale---- >Da: esiotrot@gmail.com >Data: 09/06/2010 16.54 >A: "coviolo@libero.it"<coviolo@libero.it> >Cc: <pgsql-novice@postgresql.org> >Ogg: Re: [NOVICE] problem with variable > >On 9 June 2010 16:51, Michael Wood <esiotrot@gmail.com> wrote: >> Hi >> >> On 9 June 2010 16:24, coviolo@libero.it <coviolo@libero.it> wrote: >>> something like this: >>> >>> IF (TG_OP = 'UPDATE') THEN >>> EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY >>> CHECK (nome_tabella = '''||NEW.nome_tabella||'''::text)) >>> INHERITS (database_t); >>> >>> 3 quotes first and 3 quotes after the second variable? >> >> Just a guess, but I think this is what you want: >> >> IF (TG_OP = 'UPDATE') THEN >> EXECUTE 'CREATE TABLE ' || NEW.nome_tabella || ' (ordinativo serial PRIMARY KEY >> CHECK (nome_tabella = "' || NEW.nome_tabella || '"::text)) >> INHERITS (database_t);' >> >> i.e. you want: >> >> CREATE TABLE table_name (x serial PRIMARY KEY >> CHECK (column_name = "table_name"::text)) >> INHERITS (database_t); > >Sorry, I was talking nonsense. You want column_name = 'table_name' >but because the ' will be inside a quoted string, you need to double >it. So you were right. Use ...nome_tabella = ''' || NEW.nome_tabella >|| '''::text... > >-- >Michael Wood <esiotrot@gmail.com> > >-- >Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-novice >