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

Search Discussions

  • Andreas Kretschmer at Dec 7, 2005 at 3:21 pm

    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 ===
  • Luis Silva at Dec 7, 2005 at 3:43 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 7, '05 at 3:12p
activeDec 7, '05 at 3:43p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Luis Silva: 2 posts Andreas Kretschmer: 1 post

People

Translate

site design / logo © 2022 Grokbase