Is there a way I can use currval() without selecting it more than once?

Question

I want to be able to use the current value of an auto ID by giving it some kind of name and just referring to it (instead of calling SELECT for each record):

Requests table has a serial column named requestid.

insert into testschema.requests (chid) values
(1);

I want to use select currval() multiple times without having to use SELECT for each line:

insert into testschema.requestinfo (requestid, reportid, amount) values
((select currval('testschema.requests_requestid_seq')), 10, 40),
((select currval('testschema.requests_requestid_seq')), 15, 29),
((select currval('testschema.requests_requestid_seq')), 40, 4);

I've tried placing the select on its own line with no success:

SELECT currval('testschema.requests_requestid_seq') as reqid;
INSERT INTO testschema.requestinfo(requestid, reportid) VALUES
 (reqid, 2);

Bottom line: I want to refer to the currval without having to use SELECT currval() for each line.

Answer 1

You would not need a subquery in your statement. The bare function does the job:

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
VALUES (currval('testschema.requests_requestid_seq'), 10, 40)
     , (currval('testschema.requests_requestid_seq'), 15, 29)
     , (currval('testschema.requests_requestid_seq'), 40, 4);

But currval() only makes sense if you have (implicitly or explicitly) called nextval() for the same sequence before in your session. In this case, and if you have not called nextval() for any other sequences in the meantime, you can simplify with lastval().

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
VALUES (lastval(), 10, 40)
     , (lastval(), 15, 29)
     , (lastval(), 40, 4);

Either way, if you have many rows like this, look to @dezso's answer for a way to avoid typing the function repeatedly.

currval() and lastval() are volatile functions:

SELECT proname, provolatile
FROM   pg_proc
WHERE  proname IN ('currval', 'lastval');

proname	provolatile
currval	v
lastval	v

Meaning they will still be evaluated for every row that way.
To also avoid repeated execution of the function, move it to a separate CTE or subquery and CROSS JOIN to your input values:

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
SELECT id, v.*
FROM   lastval() id
CROSS  JOIN (VALUES (10,40), (15,29), (40,4)) v;

fiddle

Answer 2

Try

WITH vals (reportid, amount) AS (
    VALUES (10, 40),
           (15, 29),
           (40, 4)
)
INSERT INTO testschema.requestinfo (requestid, reportid, amount) 
SELECT currval('testschema.requests_requestid_seq'), reportid, amount
FROM vals;

This is a nice generic solution for similar tasks. It neatly separates the fixed values from the dynamically obtained ones.

With big data sets I would swap the VALUES 'table' with a real temporary table, though.