Move/fetch cursor in loop (PostgreSQL)

Question

I need to create trigger to check if new inserted element's id isn't repeated. The problem is in the LOOP statement, console spit out the error:

CONTEXT:  SQL statement in PL/PgSQL function "foo" near line 7
LINE 1: move forward 1 from  $1

Here is my function:

create function foo() returns trigger as'
declare xyz cursor for select id from accounts; 
    begin
        LOOP
            if NEW.id = xyz then
                raise notice ''Id is just used!'';
            else
                move forward 1 from xyz;
            end if;
        END LOOP;
    return NEW;
    close xyz;
    end;
' language 'plpgsql';

create trigger foo before insert on accounts for each
row execute procedure foo();

You haven't opened the cursor. Before the LOOP do OPEN xyz, or use FOR expression instead of loop, like documentation says — J0HN
– J0HN, Commented May 21, 2013 at 17:56
Adding OPEN xyz doesn't help. I forgot to add I use Postgre SQL 8.1 version (in case it's relevant). — kozooh
– kozooh, Commented May 21, 2013 at 18:19
You don't need that trigger at all. Creating a unique index on the column id serves the same purposes, is way faster, scales better and is much more reliable. — user330315
– user330315, Commented May 21, 2013 at 19:10

Pavel Stehule · Accepted Answer · 2013-05-21 18:53:49Z

1

Your example has no sense (you can't compare scalar value with cursor). Cursor self is like pointer without any value.

  if NEW.id = xyx then

Why you don't do

BEGIN
  IF EXISTS(SELECT * FROM accounts a WHERE a.id = NEW.id) THEN
    RAISE NOTICE ''Id is used''; -- better RAISE EXCEPTION
  END IF;
  RETURN NEW;
END;

second nonsense

  RETURN NEW;
  CLOSE xyz; -- no statement is executed after RETURN

edited May 21, 2013 at 18:53

answered May 21, 2013 at 18:36

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

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

4 Comments

kozooh Over a year ago

Great, that's exactly what I need, thx! BTW, is it possible to get specific value from the cursor?

Pavel Stehule Over a year ago

sure, look on FETCH statement, but in this use case is using a cursor really bad idea. You can find a articles on net about "why don't use ISAM style in stored procedures". Joe Celko wrote good books.

Pavel Stehule Over a year ago

Usually you have to minimize a cycles in stored procedures (databases are not a arrays) - there are lot of examples of wrong using of cursor, cycles, and similar danger things in SQL applications, so be careful! Sometimes it has sense, but not too often.

kozooh Over a year ago

Yeah, I'm used to deploy loops because of programming. Thanks for advice, I'll be aware of that!

Collectives™ on Stack Overflow

Move/fetch cursor in loop (PostgreSQL)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related