insert of multi columns with "with" in postgresql

Question

My query is like this

WITH p AS (  select idA from TableA   )  
insert into TableB(idB,idA,city,data) 
   select  sp_get_id('TableB'),p.idA from p,'TO',now()

Note: sp_get_id('TableB') is a stored procedure to generated id. I execute the query above and it is wrong.

"It is wrong". Are you getting an error? Could you share it? — JNevill
– JNevill, Commented Dec 11, 2017 at 17:14
Also, what is ,'TO',now() supposed to do? Perhaps if you could better explain what, overall, you are trying to do here and the relevant code like... what your proc is doing, (in and out params), or why a CTE? — JNevill
– JNevill, Commented Dec 11, 2017 at 17:16
thank you JNeil, now,Mureinik has helped me to solve the problem — martinwang1985
– martinwang1985, Commented Dec 12, 2017 at 8:46

Mureinik · Accepted Answer · 2017-12-11 17:26:51Z

1

The the terms you're trying to insert are simple scalars. You could just query them from p:

WITH p AS (SELECT idA FROM TableA)  
INSERT INTO TableB (idB, idA, city, data) 
SELECT sp_get_id('TableB'), idA, 'TO', NOW()
FROM   p

But, frankly, I think that using a CTE here just complicates things (assuming the question itself isn't a simplification of the real problem):

INSERT INTO TableB (idB, idA, city, data) 
SELECT sp_get_id('TableB'), idA, 'TO', NOW()
FROM   TableA

answered Dec 11, 2017 at 17:26

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1