PostgreSQL query has no destination for result data

Question

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?

Are you forgetting INTO?

Nick
– Nick

2016-08-18 01:36:04 +00:00
Commented Aug 18, 2016 at 1:36 — Nick
– Nick, Commented Aug 18, 2016 at 1:36

Patrick · Accepted Answer · 2016-08-18 01:37:42Z

3

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);

edited Aug 18, 2016 at 1:37

answered Aug 18, 2016 at 1:35

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Josh Smith Over a year ago

Thank you! I figured it was something trivial like that. This is my first trigger ever. The downsides of relying heavily on ORMs!

Patrick Over a year ago

Oh yeah, ORMs are evil! I do all my joining and complex queries in PG and expose them as an updatable view. The ORM then works with a simple relation. Much more efficient and clean. Good luck with plain-vanilla PG!

Collectives™ on Stack Overflow

PostgreSQL query has no destination for result data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related