On 06 Apr 2014, at 2:14, Ben Hoyt <benhoyt@xxxxxxxxx> 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.
>
> -Ben
If you want to be able to rename your original files to the new names using the information returned from your INSERT, you will probably have to add the original name to the table you’re inserting to - I don’t see any way to get to the original name 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) that has the original file name as some sort of placeholder field, without actually inserting the data into the underlying table. 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 or similar. Put that in a trigger and use the original file name in the INSERT statement and the trigger will take care of the rest. There are some caveats there though, such as not making the database wait with processing the remainder of your transaction 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@xxxxxxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
> 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
