From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ben Hoyt <benhoyt(at)gmail(dot)com>, Francisco Olarte <folarte(at)peoplecall(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Any way to insert rows with ID used in another column |
Date: | 2014-04-06 00:30:14 |
Message-ID: | 5340A016.1090002@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | bricklen | 2014-04-06 02:02:09 | Re: Log file monitoring and event notification |
Previous Message | Ben Hoyt | 2014-04-06 00:14:19 | Re: Any way to insert rows with ID used in another column |