I trying to make function, that merge where
select query return result as value(not table) and
insert into that use value from select statement.
The function where argument gets from user, and this argument provide into
select query on
table_foo that return value
id where I try set and use in
insert into to diff table(
table_bar). So I little bit confused. Here sample of code:
create or replace function my_func(taskID integer) returns integer language plpgsql as $function$ declare jobID int; begin return query ( select id from table_foo where task_id = taskID ); --that must return values such as id that i need set jobID = id --trying set previosly returned value to new to use it in next statement: insert into table_bar (job_id) values (jobID); end; $function$ ;
No need for plpgsql and no need for two queries either. Combine the INSERT and SELECT, and use RETURNING.
CREATE OR REPLACE FUNCTION my_func(taskID INTEGER) RETURNS INTEGER LANGUAGE SQL AS $$ INSERT INTO table_bar(job_id) SELECT id FROM table_foo WHERE task_id = taskID RETURNING id; $$;
Answered By – Frank Heikens
Answer Checked By – Senaida (BugsFixing Volunteer)