Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE job(id int, reference int, job_title text, status text, delete_flag boolean);
INSERT INTO job VALUES
  (1, 1, 'foo_job', 'xy  abc', false)
, (2, 1, 'bar_job', '12 abc', false)
, (2, 1, 'bar2_job', '12defabc', false)
, (3, 1, 'baz_job', '00abc', true)
;

CREATE TABLE company(id int, company text, active boolean, delete_flag boolean);
INSERT INTO company VALUES
  (1, 'foo_company', true, false)
, (2, 'bar_company', true, false)
, (3, 'baz_company', false, false)
;

CREATE OR REPLACE FUNCTION f_test(VARIADIC int[])
  RETURNS TABLE (id int, reference int, job_title text, status text)
  LANGUAGE sql AS
$func$
   SELECT j.id, j.reference, j.job_title
        , ltrim(right(j.status, -2)) AS status
   FROM   company c
   JOIN   job     j USING (id)
   WHERE  c.active
   AND    NOT c.delete_flag
   AND    NOT j.delete_flag
   AND   (j.id = ANY($1) OR '{-1}'::int[] = $1)
   ORDER  BY j.job_title
$func$;

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 3



CREATE FUNCTION


SELECT * FROM  f_test(1,2,3);


id
reference
job_title
status


2
1
bar2_job
defabc

2
1
bar_job
abc

1
1
foo_job
abc



SELECT 3


SELECT * FROM  f_test(VARIADIC '{1,2,3}'::int[]);


id
reference
job_title
status


2
1
bar2_job
defabc

2
1
bar_job
abc

1
1
foo_job
abc



SELECT 3