Thread: plpgsql - Inserting DEFAULT Value.

Hi all,

I need to protect a SERIAL column from having a value inserted into it (other
than the default sequence) and keep that value from being tampered with.  So
I created a function which is called by a trigger.  Unfortunately, I don't
know how to assign DEFAULT to the id column.  Can anyone tell me how I can
fix my function or is there another easier way that doesn't need triggers or
functions?  Here is what I got so far.

CREATE OR REPLACE FUNCTION id_protect()
RETURNS TRIGGER AS '
        BEGIN
                IF TG_OP = ''INSERT'' THEN
                        NEW.id := DEFAULT;   -- here is the problem :(
                        RETURN NEW;
                ELSIF TG_OP = ''UPDATE'' THEN
                        NEW.id := OLD.id;
                        RETURN NEW;
                ELSE
                        RETURN NEW;
                END IF;
        END;
' LANGUAGE PLPGSQL;

Important point:  I need this to be a generic function.  I got multiple tables
that have a id column, each with their own unique sequences, and I want to
use the same function for all of them.  So NEW.id := nextval('some_sequence')
can't work.

Thank you all,

Vams

Vams <vmudrageda@charter.net> writes:
> Important point: I need this to be a generic function.  I got multiple
> tables that have a id column, each with their own unique sequences,
> and I want to use the same function for all of them.  So NEW.id :=
> nextval('some_sequence') can't work.

Nonetheless, that is the direction you want to go.

Consider passing the appropriate sequence name to the function as a
trigger parameter.

            regards, tom lane

On Sunday 20 June 2004 03:37 pm, Tom Lane wrote:
> Consider passing the appropriate sequence name to the function as a
> trigger parameter.

That was going to be a last resort.  So there is no way to access the DEFAULT
value that a column would get if no value or a DEFAULT was sent in an insert?
How about removing a column value so that the default action would be
performed.  Like someone would "INSERT ... VALUES (1234, ...);" and I would
remove the 1234 or something similar so that the INSERT would activate the
default constraint for the column?

Thx,
  Vams

PS.  I tried to post much sooner (like a week ago), but for some reason I was
unable to.  I emailed the admins and whoever dropped the restriction, thank
you very much.