Convert SQL-Server specific INSERT query to PostgreSQL

Question

I have a SQL Server 2008 specific query how can I execute same query in PostgreSQL.

INSERT INTO profile_answers_new (pro_ans_frn_pro_id, pro_ans_name)
SELECT pro_ans_frn_pro_id = @new_pro_id , pro_ans_name
FROM profile_answers_old WHERE profile_id = 1

How can I convert this query as a PostgreSQL specific one.

Answer 1

There are no variables in plain SQL in PostgreSQL. You can write a literal constant as is into the SELECT statement.

INSERT INTO profile_answers_new (pro_ans_frn_pro_id, pro_ans_name)
SELECT 1234, pro_ans_name     -- 1234 being @new_pro_id (integer)
FROM   profile_answers_old
WHERE  profile_id = 1

An expression like you have in your original statement (pro_ans_frn_pro_id = @new_pro_id) would be evaluated to TRUE / FALSE / NULL. You certainly don't want that.

If you wanted to hand in a parameter, you'd have to create a function like:

CREATE OR REPLACE FUNCTION foo(int)
  RETURNS void LANGUAGE sql AS
$func$
INSERT INTO profile_answers_new (pro_ans_frn_pro_id, pro_ans_name)
SELECT $1, pro_ans_name
FROM   profile_answers_old
WHERE  profile_id = 1;
$func$;

Call:

SELECT foo(1234);

But I doubt, that's what you want.