RETURN cannot have a parameter in function returning set while returning from function

Question

I have following code but this is throwing error:

RETURN cannot have a parameter in function returning set

Any idea on how I can fix it and why I am getting this error?

DROP FUNCTION IF EXISTS public.get_services_connected_for_workspace;
DROP FUNCTION IF EXISTS public.get_services_connected_data;

CREATE OR REPLACE FUNCTION public.get_services_connected_for_workspace(_workspace_id BIGINT)
 RETURNS SETOF services_connected_response
 LANGUAGE plpgsql
AS $function$
DECLARE
  results services_connected_response[];
  services_connected_row services_connected_response;
BEGIN
  SELECT ARRAY[
    ('github', COALESCE(service_github.auto_invite, false), services_connected.service_github IS NOT NULL, false, COALESCE(service_github.last_synced, NULL), COALESCE(service_github.sync_status, NULL)),
    ('zoom', COALESCE(service_zoom.auto_invite, false), services_connected.service_zoom IS NOT NULL, false, COALESCE(service_zoom.last_synced, NULL), COALESCE(service_zoom.sync_status, NULL)),
    ('jira', COALESCE(service_jira.auto_invite, false), services_connected.service_jira IS NOT NULL, false, COALESCE(service_jira.last_synced, NULL), COALESCE(service_jira.sync_status, NULL)),
    ('docusign', COALESCE(service_docusign.auto_invite, false), services_connected.service_docusign IS NOT NULL, false, COALESCE(service_docusign.last_synced, NULL), COALESCE(service_docusign.sync_status, NULL)),
    ('asana', COALESCE(service_asana.auto_invite, false), services_connected.service_asana IS NOT NULL, false, COALESCE(service_asana.last_synced, NULL), COALESCE(service_asana.sync_status, NULL)),
    ('google', COALESCE(service_google.auto_invite, false), services_connected.service_google IS NOT NULL, true, COALESCE(service_google.last_synced, NULL), COALESCE(service_google.sync_status, NULL))
  ]
  INTO results
  FROM services_connected
  LEFT JOIN service_github ON services_connected.service_github = service_github.id
  LEFT JOIN service_jira ON services_connected.service_jira = service_jira.id
  LEFT JOIN service_zoom ON services_connected.service_zoom = service_zoom.id
  LEFT JOIN service_asana ON services_connected.service_asana = service_asana.id
  LEFT JOIN service_docusign ON services_connected.service_docusign = service_docusign.id
  LEFT JOIN service_google ON services_connected.service_google = service_google.id
  WHERE services_connected.workspace_id = _workspace_id;
  RETURN results;
END;
$function$;

CREATE OR REPLACE FUNCTION public.get_services_connected_data()
 RETURNS SETOF services_connected_response
 LANGUAGE plpgsql
AS $function$
DECLARE
  _workspace_id BIGINT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'workspace_id')::BIGINT;
BEGIN
   RETURN QUERY
    SELECT *
    FROM public.get_services_connected_for_workspace(_workspace_id);
END;
$function$;

Answer 1

You declared the function to return a set, but it actually returns a single array (array of composite type). Either fix the RETURNS declaration, or what's actually returned.

Or rather, consider this single function:

CREATE OR REPLACE FUNCTION public.get_services_connected_for_workspace(_workspace_id bigint DEFAULT ((current_setting('request.jwt.claims', true))::json ->> 'workspace_id')::bigint)
  RETURNS SETOF services_connected_response
  LANGUAGE sql AS
$func$
   SELECT result.*
   FROM   services_connected s
   LEFT   JOIN service_github   g ON s.service_github   = g.id
   LEFT   JOIN service_zoom     z ON s.service_zoom     = z.id
   LEFT   JOIN service_jira     j ON s.service_jira     = j.id
   LEFT   JOIN service_docusign d ON s.service_docusign = d.id
   LEFT   JOIN service_asana    a ON s.service_asana    = a.id
   LEFT   JOIN service_google   g ON s.service_google   = g.id
   CROSS  JOIN LATERAL (
      VALUES
        ('github'  , COALESCE(g.auto_invite, false), s.service_github   IS NOT NULL, false, COALESCE(g.last_synced, null), COALESCE(g.sync_status, null))
      , ('zoom'    , COALESCE(z.auto_invite, false), s.service_zoom     IS NOT NULL, false, COALESCE(z.last_synced, null), COALESCE(z.sync_status, null))
      , ('jira'    , COALESCE(j.auto_invite, false), s.service_jira     IS NOT NULL, false, COALESCE(j.last_synced, null), COALESCE(j.sync_status, null))
      , ('docusign', COALESCE(d.auto_invite, false), s.service_docusign IS NOT NULL, false, COALESCE(d.last_synced, null), COALESCE(d.sync_status, null))
      , ('asana'   , COALESCE(a.auto_invite, false), s.service_asana    IS NOT NULL, false, COALESCE(a.last_synced, null), COALESCE(a.sync_status, null))
      , ('google'  , COALESCE(g.auto_invite, false), s.service_google   IS NOT NULL, true,  COALESCE(g.last_synced, null), COALESCE(g.sync_status, null))
   ) result
   WHERE  s.workspace_id = _workspace_id;
$func$;

Typically it's more convenient to return a set of rows than the awkward array of records you have now. But you seem to want one row for each service you join to - if a row is found in services_connected.

Use a VALUES expression in a LATERAL subquery to pivot the single result row into a set of rows. See:

• Postgres: convert single row to multiple rows (unpivot)

Plain SQL does the job. No need for PL/pgSQL here. See:

• Difference between language sql and language plpgsql in PostgreSQL functions

No need for a second function to nest the first. This single function does it all. I use the value derived from your custom setting request.jwt.claims as DEFAULT input. So you can call the function without parameter:

SELECT * FROM public.get_services_connected_for_workspace();

Or you can pass any bigint to overrule the default:

SELECT * FROM public.get_services_connected_for_workspace(bigint '123');

See:

• Can I make a plpgsql function return an integer without using a variable?

I also use table aliases to shorten the code a lot and make it easier to read. Optional, but recommended.