Thread: Function Question

I am trying to create a function that gets fired when a new row is
inserted. I can get the following function built but my problem is
that the sequence that is created is called "NEW.key" rather than the
value that is being inserted (it should be a UUID).

I have tried putting the key into a variable and using no quotes but
it either errors or gives me "NEW.key" rather than "whatever-uuid-was
inserted".

Please can anybody give me some pointers?

Thanks
Jake

CREATE OR REPLACE FUNCTION public.setup_sequence()
                RETURNS trigger
                LANGUAGE plpgsql
                AS $$
                    DECLARE
                        sequence_name UUID;
                    BEGIN
                        IF (
                            TG_OP = 'INSERT' AND
                            NEW.type = 'auto_increment'
                        ) THEN
                            SELECT NEW.key INTO STRICT sequence_name;
                            -- Set up the sequence
                            CREATE SEQUENCE \"NEW.key\";
                            -- Set the start value
                            --SELECT SETVAL(sequence_name, NEW.auto_increment_default);
                        END IF;
                        RETURN NEW;
                    END;

Jake Stride <jake@stride.me.uk> writes:
> I am trying to create a function that gets fired when a new row is
> inserted. I can get the following function built but my problem is
> that the sequence that is created is called "NEW.key" rather than the
> value that is being inserted (it should be a UUID).

Instead of this:

>             CREATE SEQUENCE \"NEW.key\";

you need to do something like this:

            EXECUTE 'CREATE SEQUENCE ' || quote_ident(NEW.key);

The reason is that utility statements don't accept parameters natively,
so you have to construct the command as a string and then EXECUTE it.

            regards, tom lane