CODEWITHSUNDEEP
postgresqlfunctionplpgsql
Ellebjerg
Ellebjerg

Reputation: 3

Why does function i PostgreSQL not raise exception?

I have a before insert or update trigger which is supposed to validate values of two columns in the inserted/updated data, raise an exception if the data are not valid or - if valid - add values to two other columns in the insert/update.

However, when I test with invalid data I get "ERROR: query has no destination for result data. HINT: If you want to discard the results of a SELECT, user PERFORM instead. CONTEXT: PL/pgSQL function before_insert_update() line 3 at SQL statement."

I have tried to read the PostgreSQL documentation and I have googled for explanations, but I just have to admin that my understanding of SQL is not sufficient to make it work.

My function is

CREATE or replace FUNCTION before_insert_update()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
begin
    select * from addresses
    where addresses.roadname = NEW.roadname and
        addresses.housenumber = NEW.housenumber;

    if @@ROWCOUNT > 0 then
        begin
            select addresses.roadcode,
                    addresses.geom
                into NEW.roadcode, NEW.geom
            from addresses
            where addresses.roadname = NEW.roadname and
                addresses.housenumber = NEW.housenumber;

            return NEW;
        end;
    else
        declare _adresse text;
        begin
            _address := concat(NEW.roadname, ' ', NEW.housenumber);
            raise exception 'The address does not exist: %', _address
                using hint = 'Check the address here: https://danmarksadresser.dk/adresser-i-danmark/';
        end;
    end if;
END
$BODY$;

The trigger is pretty straight forward

BEFORE INSERT OR UPDATE
ON table
FOR EACH ROW
EXECUTE PROCEDURE before_insert_update();

What is it that I am doing wrong?

Upvotes: 0

Views: 1441

Answers (2)

user330315
user330315

Reputation:

There is no need to run a test if the row is present. Just select it, and check the status afterwards:

CREATE or replace FUNCTION before_insert_update()
    RETURNS trigger
    LANGUAGE plpgsql
    COST 100
    VOLATILE NOT LEAKPROOF
AS 
$BODY$
begin
  select addresses.roadcode, addresses.geom
      into NEW.roadcode, NEW.geom
  from addresses
  where addresses.roadname = NEW.roadname and
        addresses.housenumber = NEW.housenumber;
        
  if found then 
    return new;
  end if;
    
  raise exception 'The address does not exist: %', concat(NEW.roadname, ' ', NEW.housenumber)
    using hint = 'Check the address here: https://danmarksadresser.dk/adresser-i-danmark/';
END
$BODY$;

Upvotes: 2

sticky bit
sticky bit

Reputation: 37472

Looks like you're trying to use SQL Server specific code in Postgres, which isn't going to work of course.

PL/pgSQL doesn't allow SELECTs just anywhere, when the result isn't passed to somehwere, like variables, etc.. And there is no @@ROWCOUNT. From what I can guess what you want to do, you can use EXISTS and your query for the condition of the IF.

CREATE 
 OR REPLACE FUNCTION before_insert_update()
                     RETURNS trigger
AS
$$
BEGIN
  IF EXISTS(SELECT *
                   FROM addresses
                   WHERE addresses.roadname = new.roadname
                         AND addresses.housenumber = new.housenumber) THEN
    SELECT addresses.roadcode,
           addresses.geom
           INTO new.roadcode,
                new.geom
           FROM addresses
           WHERE addresses.roadname = new.roadname
                 AND addresses.housenumber = new.housenumber;

    RETURN new;
  ELSE
    RAISE EXCEPTION 'The address does not exist: %', concat(new.roadname, ' ', new.housenumber)
                    USING HINT = 'Check the address here: https://danmarksadresser.dk/adresser-i-danmark/';


  END IF;
END
$$
LANGUAGE plpgsql;

Upvotes: 0

Related Questions