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

If only there was some documentation. Oh there is: 43.6.1.2. RETURN NEXT and RETURN QUERY. — Belayer
– Belayer, Commented Sep 3, 2023 at 19:50

Erwin Brandstetter · Accepted Answer · 2023-09-04 21:14:28Z

2

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.

edited Sep 4, 2023 at 21:14

answered Sep 3, 2023 at 21:22

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alwaysblue Over a year ago

Thanks, This is super helpful. Sorry, I couldn't understand the context/reference of this

A plain UNION ALL with queries on the subsidiary tables returns no row if no matching row is found. We would have to include the main table services_connected in an OUTER JOIN every time to defend against that. Or we use a VALUES expression in a LATERAL subquery. Shorter and faste

Can you please explain why did you mentioned this? (1/2)

Alwaysblue Over a year ago

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:

. Thanks, the idea here is the first function could be called by other functions as well where I need to know which services are active. The function get_services_connected_data, the one with ((current_setting('request.jwt.claims', true))::json ->> 'workspace_id')::bigint) is the function which could is called by frontend. (2/2)

Erwin Brandstetter Over a year ago

@Alwaysblue: ① I removed the tangent discussing a possible alternative with UNION ALL, since I did not include the alternative (inferior) solution after all. ② I don't see what a wrapper function would add over my single function.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related