FAQ
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

Search Discussions

  • Tim Landscheidt at Jun 9, 2010 at 12:05 pm

    "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
  • Coviolo at Jun 9, 2010 at 2:31 pm
    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" 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
  • Michael Wood at Jun 9, 2010 at 2:51 pm
    Hi
    On 9 June 2010 16:24, 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>
  • Michael Wood at Jun 9, 2010 at 2:54 pm

    On 9 June 2010 16:51, Michael Wood wrote:
    Hi
    On 9 June 2010 16:24, 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>
  • Coviolo at Jun 9, 2010 at 3:23 pm
    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 wrote:
    Hi
    On 9 June 2010 16:24, 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
  • Coviolo at Jun 10, 2010 at 7:31 am
    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 wrote:
    Hi
    On 9 June 2010 16:24, 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 9, '10 at 10:37a
activeJun 10, '10 at 7:31a
posts7
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase