Postgres Pro
Downloads
Home   >   mailing lists

Thread: trigger function with arguments from a sql command

trigger function with arguments from a sql command

From
"Luis Silva"
Date:
hi there, i'm having a big problem. I have this table
 
 
CREATE TABLE test {
 
    id int8 NOT NULL,
    asname varchar(80) NOT NULL,
    priority int2 NOT NULL,
    serviceid,
    ...
 
    CONSTRAIN PRIMARY KEY (id),
    CONSTRAIN FOREIGN KEY (serviceid) REFERENCES service (id) MATCH FULL
}
 
and i need to make a trigger function that accept arguments insert in a INSERT INTO. The priority value must be unique for the same serviceid but it can be the same for different serviceid. how can i do that, pass the arguments of a command? tks in advance
 
 

Re: trigger function with arguments from a sql command

From
"A. Kretschmer"
Date:
am  07.12.2005, um 15:09:00 -0000 mailte Luis Silva folgendes:
> hi there, i'm having a big problem. I have this table
>
>
> CREATE TABLE test {
>
>     id int8 NOT NULL,
>     asname varchar(80) NOT NULL,
>     priority int2 NOT NULL,
>     serviceid,
>     ...
>
>     CONSTRAIN PRIMARY KEY (id),
>     CONSTRAIN FOREIGN KEY (serviceid) REFERENCES service (id) MATCH FULL
> }
>
> and i need to make a trigger function that accept arguments insert in
> a INSERT INTO. The priority value must be unique for the same
> serviceid but it can be the same for different serviceid. how can i do
> that, pass the arguments of a command? tks in advance

1. you can't pass arguments to a trigger
2. you can create a unique index on (priority,serviceid).

test=# create table foobar (id int not null, prio int not null, serviceid int not null);
CREATE TABLE
test=# create unique index idx_foobar on foobar (prio, serviceid);
CREATE INDEX
test=# insert into foobar values (1,1,1);
INSERT 0 1
test=# insert into foobar values (1,1,2);
INSERT 0 1
test=# insert into foobar values (1,2,2);
INSERT 0 1
test=# insert into foobar values (1,2,2);
ERROR:  duplicate key violates unique constraint "idx_foobar"


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Re: trigger function with arguments from a sql command

From
"Luis Silva"
Date:
It works!!!! Tks a lot, your the men!!


----- Original Message -----
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, December 07, 2005 3:20 PM
Subject: Re: [NOVICE] trigger function with arguments from a sql command


> am  07.12.2005, um 15:09:00 -0000 mailte Luis Silva folgendes:
>> hi there, i'm having a big problem. I have this table
>>
>>
>> CREATE TABLE test {
>>
>>     id int8 NOT NULL,
>>     asname varchar(80) NOT NULL,
>>     priority int2 NOT NULL,
>>     serviceid,
>>     ...
>>
>>     CONSTRAIN PRIMARY KEY (id),
>>     CONSTRAIN FOREIGN KEY (serviceid) REFERENCES service (id) MATCH FULL
>> }
>>
>> and i need to make a trigger function that accept arguments insert in
>> a INSERT INTO. The priority value must be unique for the same
>> serviceid but it can be the same for different serviceid. how can i do
>> that, pass the arguments of a command? tks in advance
>
> 1. you can't pass arguments to a trigger
> 2. you can create a unique index on (priority,serviceid).
>
> test=# create table foobar (id int not null, prio int not null, serviceid
> int not null);
> CREATE TABLE
> test=# create unique index idx_foobar on foobar (prio, serviceid);
> CREATE INDEX
> test=# insert into foobar values (1,1,1);
> INSERT 0 1
> test=# insert into foobar values (1,1,2);
> INSERT 0 1
> test=# insert into foobar values (1,2,2);
> INSERT 0 1
> test=# insert into foobar values (1,2,2);
> ERROR:  duplicate key violates unique constraint "idx_foobar"
>
>
> HTH, Andreas
> --
> Andreas Kretschmer    (Kontakt: siehe Header)
> Heynitz:  035242/47212,      D1: 0160/7141639
> GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
> ===    Schollglas Unternehmensgruppe    ===
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>