0

I have the following composite data type in a postgres db:

CREATE TYPE IncreaseCredentialsType AS
(
  increase_account_id text,
  api_key text,
  url text
);

I'm returning a value of this type at the end of a function, like so. The exception I'm throwing just to see what the result looks like before the function returns.

result := (increase_account_id, api_key, url)::IncreaseCredentialsType;
RAISE EXCEPTION 'get_increase_credentials about to return %', result;
RETURN result;

When I do that, it prints:

ERROR:  get_increase_credentials about to return (,,https://increase.com/api/v1)

(By the way, the first two parameters are blank on purpose). I'm then commenting out that exception and letting the control flow back to the calling function to see what I get in that context:

SELECT public.get_increase_credentials(account_id) INTO result;
RAISE EXCEPTION 'get_increase_credentials returned %', result;

When I do that, I get:

ERROR:  get_increase_credentials returned ("(,,https://increase.com/api/v1)",,)

The first field (result.increase_account_id) has the entire result as a string, with the parentheses and commas. What am I doing wrong that is making this handoff not work? In both cases, the variable I'm assigning to is of type IncreaseCredentialsType.

2
  • I think the problem is outside of that function, how are you getting the return value of the function?
    – Jasen
    Jan 27, 2023 at 7:37
  • I've included that information above. As you can see, I'm doing a SELECT INTO a variable of the same type (IncreaseCredentialsType).
    – cayblood
    Jan 27, 2023 at 13:54

1 Answer 1

0

I learned that it was easier to return multiple values from a function by using IN/OUT declarations in the method signature, as follows:

CREATE OR REPLACE FUNCTION public.get_increase_credentials(
  IN acct_id uuid,
  OUT rval_increase_account_id text,
  OUT rval_api_key text,
  OUT rval_url text
)
...

And in the caller:

...
DECLARE
  account_id uuid;
  result record;
BEGIN
  SELECT a.id FROM accounts a, account_user b WHERE a.id = b.account_id AND b.user_id = auth.uid() INTO account_id;
  result := public.get_increase_credentials(account_id);
  -- refer to the return values with:
  -- result.rval_increase_account_id
  -- result.rval_api_key
  -- result.rval_url
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.