rpc() - value is not a valid list

[louis030195]

Describe the bug

rpc() crashes (calling a db function) with following error:

./tests/test_claims.py::test_get_claims Failed: [undefined]postgrest.exceptions.APIError: {'provider': 'email', 'providers': ['email'], 'claims_admin': True}
self = <postgrest._sync.request_builder.SyncFilterRequestBuilder object at 0x103d5a680>

def execute(self) -> APIResponse:
    """Execute the query.

    .. tip::
        This is the last method called, after the query is built.

    Returns:
        :class:`APIResponse`

    Raises:
        :class:`APIError` If the API raised an error.
    """
    r = self.session.request(
        self.http_method,
        self.path,
        json=self.json,
        params=self.params,
        headers=self.headers,
    )
    try:
        if (
            200 <= r.status_code <= 299
        ):  # Response.ok from JS (https://developer.mozilla.org/en-US/docs/Web/API/Response/ok)

          return APIResponse.from_http_request_response(r)

env/lib/python3.10/site-packages/postgrest/_sync/request_builder.py:66:

---

cls = <class 'postgrest.base_request_builder.APIResponse'>
request_response = <Response [200 OK]>

@classmethod
def from_http_request_response(
    cls: Type[APIResponse], request_response: RequestResponse
) -> APIResponse:
    try:
        data = request_response.json()
    except JSONDecodeError as e:
        return cls(data=[], count=0)
    count = cls._get_count_from_http_request_response(request_response)

  return cls(data=data, count=count)

env/lib/python3.10/site-packages/postgrest/base_request_builder.py:162:

---

???
E pydantic.error_wrappers.ValidationError: 1 validation error for APIResponse
E data
E value is not a valid list (type=type_error.list)

pydantic/main.py:342: ValidationError

The above exception was the direct cause of the following exception:

def test_get_claims():
    client = get_client()

  claims = get_claims(client, USER_ID)

tests/test_claims.py:11:

---

middlewares/claims.py:27: in get_claims
res = client.rpc("get_claims", {"uid": uid}).execute()

Using get_claims function from:

https://github.com/wantpinow/supabase-custom-claims/blob/patch-1/install.sql

Is it because the function returns a jsonb ?

To Reproduce

Steps to reproduce the behavior:

1. Install the function get_claims from this PR https://github.com/wantpinow/supabase-custom-claims/blob/patch-1/install.sql
2. Call it from latest version of supabase-py

Expected behavior

Returns the function output

Desktop (please complete the following information):

• OS: MacOS 13.3
• Version supabase==1.0.2

[e-gons]

Similar issue here where a stored function returns a NUMERIC

[modyabhi]

Bumping this, same issue here when the function returns a value on version 1.0.3.

FUNCTION CODE:

CREATE OR REPLACE FUNCTION insert_subscription(
    usr text,
    id text,
    code text,
    expiry date DEFAULT NULL
)
RETURNS text AS $$
DECLARE
  result text;
BEGIN
  -- Check if the subscription already exists
  IF EXISTS (
      SELECT 1 FROM subscription
      WHERE discord_id = insert_subscription.id
        AND asx_code = insert_subscription.code
  ) THEN
      result := 'Error: Subscription already exists.';
  ELSE
      -- Insert a new row into the subscription table
      INSERT INTO subscription (discord_user, discord_id, asx_code, subscription_expiry)
      VALUES (usr, id, code, expiry);
      result := 'Success: Subscription inserted.';
  END IF;
  
  RETURN result;
END;
$$ LANGUAGE plpgsql;

PYTHON MODULE:

from dotenv import load_dotenv
import os
from supabase import create_client, Client
SUPABASE_URL = os.environ.get("SUPABASE_URL")
SUPABASE_KEY = os.environ.get("SUPABASE_SECRET_KEY")
supabase = create_client(SUPABASE_URL, SUPABASE_KEY)
payload = {'usr': 'abcd', 'id': 1234, 'code': 'WPC'}
res = supabase.rpc('insert_subscription',payload).execute()

ERROR:

ValidationError                           Traceback (most recent call last)
File [~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/postgrest/_sync/request_builder.py:66](https://file+.vscode-resource.vscode-cdn.net/Users/amody/Downloads/pyasx-master/pyasx/~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/postgrest/_sync/request_builder.py:66), in SyncQueryRequestBuilder.execute(self)
     63 if (
     64     200 <= r.status_code <= 299
     65 ):  # Response.ok from JS (https://developer.mozilla.org/en-US/docs/Web/API/Response/ok)
---> 66     return APIResponse.from_http_request_response(r)
     67 else:

File [~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/postgrest/base_request_builder.py:162](https://file+.vscode-resource.vscode-cdn.net/Users/amody/Downloads/pyasx-master/pyasx/~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/postgrest/base_request_builder.py:162), in APIResponse.from_http_request_response(cls, request_response)
    161 count = cls._get_count_from_http_request_response(request_response)
--> 162 return cls(data=data, count=count)

File [~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/pydantic/main.py:342](https://file+.vscode-resource.vscode-cdn.net/Users/amody/Downloads/pyasx-master/pyasx/~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/pydantic/main.py:342), in pydantic.main.BaseModel.__init__()

ValidationError: 1 validation error for APIResponse
data
  value is not a valid list (type=type_error.list)

During handling of the above exception, another exception occurred:

AttributeError                            Traceback (most recent call last)
Cell In[19], line 1
----> 1 res = supabase.rpc('insert_subscription',payload).execute()

File [~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/postgrest/_sync/request_builder.py:70](https://file+.vscode-resource.vscode-cdn.net/Users/amody/Downloads/pyasx-master/pyasx/~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/postgrest/_sync/request_builder.py:70), in SyncQueryRequestBuilder.execute(self)
     68         raise APIError(r.json())
     69 except ValidationError as e:
---> 70     raise APIError(r.json()) from e
     71 except JSONDecodeError as e:
     72     raise APIError(generate_default_error_message(r))

File [~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/postgrest/exceptions.py:21](https://file+.vscode-resource.vscode-cdn.net/Users/amody/Downloads/pyasx-master/pyasx/~/opt/anaconda3/envs/py10/lib/python3.10/site-packages/postgrest/exceptions.py:21), in APIError.__init__(self, error)
     19 def __init__(self, error: Dict[str, str]) -> None:
     20     self._raw_error = error
---> 21     self.message = error.get("message")
     22     self.code = error.get("code")
     23     self.hint = error.get("hint")

AttributeError: 'str' object has no attribute 'get'

[Abdul-Rehman-Liaqat]

@louis030195 @modyabhi @e-gons
I had the same issue. Was able to make it work with the Table return type in the function. For example following function is working:
CREATE OR REPLACE FUNCTION get_ids(id text) RETURNS TABLE (id text[]) AS $$ BEGIN RETURN QUERY ( SELECT array_agg(id::text) FROM ( SELECT id FROM table1 LIMIT 20 ) subquery ); END; $$ LANGUAGE plpgsql;

And I get the same error if I get any other return type i.e. json, text , array etc.

[Lionardo]

I have the same issue when calling a function. the same code worked well in javascript

async def test_func():
    result = supabase.rpc("get_headphones_products", {})
    return result

data = None

resp = await test_func()
data = resp

the response is: <postgrest._sync.request_builder.SyncFilterRequestBuilder object at 0x10fe32ee0>

[Abdul-Rehman-Liaqat]

Yes, the issue is with python handling of data returned from supabase rpc call.

[0xDeadcell]

change result = supabase.rpc("get_headphones_products", {})
TO
result = await supabase.rpc("get_headphones_products", {}).execute()

[Matias222]

I still have the issue, when trying this ans=supabase_client.rpc("count_duplicate_emails",{}) I get <postgrest._sync.request_builder.SyncFilterRequestBuilder object at 0x10fe32ee0> and when trying ans=await supabase_client.rpc("count_duplicate_emails", {}).execute() it returns AttributeError: 'int' object has no attribute 'get', it works when calling with a Post request, but not with the Supabase python library

[ShantanuNair]

Same issue as OP. Version 1.0.4

[ShantanuNair]

@0xDeadcell This did nothing for me.

[ShantanuNair]

Hey @kiwicopple, looks like RPC functionality is largely broken for supabase python, since we cannot use RPC calls which return values

[silentworks]

@ShantanuNair we have seen this issue and are currently looking into it.

[J0]

Hey,

Wanted to echo the sentiment above ^ we'll push out a fix shortly

[silentworks]

A PR has been opened to fix this issue and we will get it merged in soon supabase/postgrest-py#309

[silentworks]

This should now be available in the latest v1.1.0 of the supabase-py library.

[Joonel]

The issue is present again in v2.0.3. Here's the reproducible example code and repo https://github.com/Joonel/rpc-issue/blob/main/retriever_testing.py

[silentworks]

@Joonel we haven't changed much in the underlying postgrest-py library since the v1.1.0 release of the supabase-py library. Please provide a reproducible example repo of the issue persisting and we'll take a look into it.