Re: Any way to insert rows with ID used in another column

On 04/05/2014 05:14 PM, Ben Hoyt wrote:
> Thanks for the info, Francisco and Alban -- that looks useful.
>
> Can you see a good way in the INSERT to combine VALUES with that
> nextval() subquery? As there are some columns that are distinct for each
> row, and some that are the same or programmatically generated for each
> row. For instance, there's a "folder" column that's different for each
> inserted row, so typically I'd specify that directly in the multiple
> VALUES rows.

Still think this is something for a BEFORE INSERT TRIGGER:

test=> \d seq_test
Table "public.seq_test"
Column | Type | Modifiers

--------+-------------------+-------------------------------------------------------
id | integer | not null default
nextval('seq_test_id_seq'::regclass)
fld | character varying |
Triggers:
test_id BEFORE INSERT ON seq_test FOR EACH ROW EXECUTE PROCEDURE
id_test()

CREATE OR REPLACE FUNCTION public.id_test()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
NEW.fld := NEW.id::text || '_' || NEW.fld;
RETURN NEW;
END;
$function$

insert into seq_test(fld) values ('my_file.jpg');
insert into seq_test(fld) values ('another_file.jpg');

test=> select * from seq_test;
id | fld
----+--------------------
1 | 1_my_file.jpg
2 | 2_another_file.jpg
(2 rows)

>
> -Ben
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com