Thread: if exists select ... in plpgsql code

I'm trying to write a trigger that enforces a constraint which can't
be done via an index.  Namely, I have 4 columns which, in combination,
should be unique.  Two of those columns can have null entries in which
case, the row with the null entry should be unique when considering
the other non-null parts.


I'm running into a couple of problems.  The plpgsql parser doesn't
seem to like my trigger function since it keeps complaining when it
tries to compile it.  In order to debug, I simplified the trigger to
the following:

create function deepsky_nodups() returns opaque as '
    begin
        if (NEW.suffix is NULL and NEW.component is NULL) then
            if (exists select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix is null and
componentis null) then 
                return NULL;
            end if;
         end if;
    end;
' language 'plpgsql';

where the table deepsky is defined as

create table deepsky (
    id                  serial primary key,
    catalog             varchar (10) not null,
    entry               varchar (30) not null,
    suffix              varchar (1),            -- NGC/IC catalogues use this
    component           varchar (1),            -- NGC/IC catalogues use this
    status              varchar (2),
    mag_p               numeric (6,2),
    type                varchar (10),
    surface_brightness  numeric (6,2),
    constellation       varchar (3),
    ra                  numeric,
    decl                numeric,
    epoch               varchar (10),
    diameter            numeric (8,2),
    diameter_b          numeric (8,2),
    position_angle      numeric (6,2),
    remark              text
);

postgresql doesn't like the "if (exists select id from ...)".

How do I need to form this query to check for an existing entry?

roland
--
                       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375

Roland Roberts <roland@astrofoto.org> writes:
>             if (exists select id from deepsky where catalog = NEW.catalog and entry = NEW.entry and suffix is null
andcomponent is null) then 

> postgresql doesn't like the "if (exists select id from ...)".

Should be "if (exists (select ...))".  Note the parens in SQL92:

         <exists predicate> ::= EXISTS <table subquery>

         <table subquery> ::= <subquery>

         <subquery> ::= <left paren> <query expression> <right paren>

            regards, tom lane

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Should be "if (exists (select ...))".  Note the parens in
    Tom> SQL92:

Thanks, I should have known that since I had just written a query with
a "where" clause that had exactly that syntax.

roland
--
               PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375