Josh Smith Josh Smith - 3 months ago 31
SQL Question

PostgreSQL query has no destination for result data

I have the following trigger which is supposed to assign

number
to a post every time a new record is inserted into the
posts
table. It finds the max
number
of
posts
scoped to the
project_id
of the new record.

CREATE OR REPLACE FUNCTION assign_number()
RETURNS trigger AS
$BODY$
BEGIN
SELECT coalesce(MAX(number), 0) max_number FROM posts WHERE project_id = NEW.project_id;
NEW.number := max_number + 1;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER post_created
BEFORE INSERT ON posts
FOR EACH ROW
EXECUTE PROCEDURE assign_number();


Unfortunately, I get the syntax error
query has no destination for result data
when trying to do an
INSERT
. I've tried everything I can think of and looked at every related question on StackOverflow, but still do not seem to be able to figure this out.

Any ideas on what's wrong here? Anything obvious that I'm missing?

Answer

Something quite obvious, yes. You have not declared the variable max_number and you did not use the INTO clause:

CREATE OR REPLACE FUNCTION assign_number() RETURNS trigger AS $BODY$
DECLARE
  max_number integer;
BEGIN
  SELECT coalesce(MAX(number), 0) INTO max_number FROM posts 
  WHERE project_id = NEW.project_id;
  NEW.number := max_number + 1;
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Alternatively, you can do without the variable and place the result straight into NEW.number:

SELECT coalesce(MAX(number)+1, 1) INTO NEW.number FROM posts 
WHERE project_id = NEW.project_id;

or even:

NEW.number := (SELECT coalesce(MAX(number)+1, 1) FROM posts 
               WHERE project_id = NEW.project_id);
Comments