Function Question

Started by Jake Strideover 14 years ago2 messagesgeneral
Jump to latest
#1Jake Stride
jake@stride.me.uk

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;

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jake Stride (#1)
Re: Function Question

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