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.klaver@aklaver.com