Re: Any way to insert rows with ID used in another column - Mailing list pgsql-general

On 06 Apr 2014, at 2:14, Ben Hoyt <benhoyt@gmail.com> 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
aredistinct 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.
>
> -Ben

If you want to be able to rename your original files to the new names using the information returned from your INSERT,
youwill probably have to add the original name to the table you’re inserting to - I don’t see any way to get to the
originalname otherwise. You’d probably have to go procedural for that, either in your application or (what Adrian
suggests)in a trigger function. 

You might be able to fake it by inserting that data into a view (with an insert rule, or you can’t insert into one)
thathas the original file name as some sort of placeholder field, without actually inserting the data into the
underlyingtable. That would be a rather misleading view to people attempting to query from it later though. It’s not
“proper”.

You could even go fancy and have the file renaming be done by an (untrusted) PL language, such as plpythonu or plperlu
orsimilar. Put that in a trigger and use the original file name in the INSERT statement and the trigger will take care
ofthe rest. There are some caveats there though, such as not making the database wait with processing the remainder of
yourtransaction until the file rename operation is completed each time. 

As a final note, please don’t top-post on this list.

> On Thu, Apr 3, 2014 at 11:17 AM, Francisco Olarte <folarte@peoplecall.com> wrote:
> ( Forgot to hit reply all, so probably someone will get this twice, sorry ).
>
> Hi:
>
> On Thu, Apr 3, 2014 at 4:41 PM, Ben Hoyt <benhoyt@gmail.com> wrote:
> .....
> > Is there some way to do something like this:
> > INSERT INTO images (filename) VALUES
> > ('foo' || image_id_about_to_used_for_this_row::text || '.jpg')
> > ('bar' || image_id_about_to_used_for_this_row::text || '.jpg')
> > I tried using currval() to see if that'd work, but it gave an error, I guess
> > because I was using it multiple times per session.
>
> You normally need to call nextval before currval. Anyway, subqueries
> are your friend:
>
> psql (9.3.2)
> Type "help" for help.
>
> postgres=# create table files ( id serial primary key, file varchar);
> CREATE TABLE
> postgres=# \d+ files
>                                                   Table "public.files"
>  Column |       Type        |                     Modifiers
>           | Storage  | Stats target | Description
>
--------+-------------------+----------------------------------------------------+----------+--------------+-------------
>  id     | integer           | not null default
> nextval('files_id_seq'::regclass) | plain    |              |
>  file   | character varying |
>           | extended |              |
> Indexes:
>     "files_pkey" PRIMARY KEY, btree (id)
> Has OIDs: no
>
> postgres=# select nextval('files_id_seq'::regclass) as id  from
> generate_series(1,3);
>  id
> ----
>   1
>   2
>   3
> (3 rows)
> postgres=# select newid as id, 'image_'||cast(newid as text)||'.jpg'
> as file from ( select nextval('files_id_seq'::regclass) as newid  from
> generate_series(1,3)) as newids;
>  id |    file
> ----+-------------
>   4 | image_4.jpg
>   5 | image_5.jpg
>   6 | image_6.jpg
> (3 rows)
>
> postgres=# insert into files (id, file) select newid as id,
> 'image_'||cast(newid as text)||'.jpg' as file from ( select
> nextval('files_id_seq'::regclass) as newid  from generate_series(1,3))
> as newids returning *;
>  id |    file
> ----+-------------
>   7 | image_7.jpg
>   8 | image_8.jpg
>   9 | image_9.jpg
> (3 rows)
>
> INSERT 0 3
> postgres=# select * from files;
>  id |    file
> ----+-------------
>   7 | image_7.jpg
>   8 | image_8.jpg
>   9 | image_9.jpg
> (3 rows)
>
>
>
>     Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.