[SOLVED] User function return query and insert into

Issue

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$
;

Solution

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)

Leave a Reply

Your email address will not be published.