Thread: problem with variable

problem with variable

From

"coviolo@libero.it"

Date:

09 June 2010, 10:37:24

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

Re: problem with variable

From

Tim Landscheidt

Date:

09 June 2010, 12:05:42

"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

Re: problem with variable

From

"coviolo@libero.it"

Date:

09 June 2010, 14:31:04

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
>

Re: problem with variable

From

Michael Wood

Date:

09 June 2010, 14:51:30

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>

Re: problem with variable

From

Michael Wood

Date:

09 June 2010, 14:54:54

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>

Re: problem with variable

From

"coviolo@libero.it"

Date:

09 June 2010, 15:23:46

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
>

Re: problem with variable

From

"coviolo@libero.it"

Date:

10 June 2010, 07:31:50

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
>