Postgres Procedure RPC with JS API

I created the procedure below with the web console:

CREATE PROCEDURE transact(id TEXT, created_at timestamptz, til TEXT, fra TEXT, beskrivelse TEXT, value float8)
LANGUAGE SQL
AS $BODY$
INSERT INTO transaktioner VALUES (id, created_at, til, fra, beskrivelse, value);
UPDATE kunder SET saldo = kunder.saldo - value WHERE id = fra;
UPDATE kunder SET saldo = kunder.saldo + value WHERE id = til;
$BODY$;

CREATE PROCEDURE transact(id TEXT, created_at timestamptz, til TEXT, fra TEXT, beskrivelse TEXT, value float8)
LANGUAGE SQL
AS $BODY$
INSERT INTO transaktioner VALUES (id, created_at, til, fra, beskrivelse, value);
UPDATE kunder SET saldo = kunder.saldo - value WHERE id = fra;
UPDATE kunder SET saldo = kunder.saldo + value WHERE id = til;
$BODY$;

but when I try to call it with the SB JS API:

interface Transaktion {
    id: string,
    created_at: string,
    fra: string,
    til: string,
    value: number,
    beskrivelse?: string,
}
const transaktionKomplet: Transaktion = generateTransactionSomehow():
console.log(await supabase.rpc("transact", transaktionKomplet));

interface Transaktion {
    id: string,
    created_at: string,
    fra: string,
    til: string,
    value: number,
    beskrivelse?: string,
}
const transaktionKomplet: Transaktion = generateTransactionSomehow():
console.log(await supabase.rpc("transact", transaktionKomplet));

, it throws the error:

{
  error: {
    code: 'PGRST202',
    details: null,
    hint: 'If a new function was created in the database with this name and parameters, try reloading the schema cache.',
    message: 'Could not find the public.transact(beskrivelse, created_at, fra, id, til, value) function or the public.transact function with a single unnamed json or jsonb parameter in the schema cache'
  },
  data: null,
  count: null,
  status: 404,
  statusText: 'Not Found'
}

{
  error: {
    code: 'PGRST202',
    details: null,
    hint: 'If a new function was created in the database with this name and parameters, try reloading the schema cache.',
    message: 'Could not find the public.transact(beskrivelse, created_at, fra, id, til, value) function or the public.transact function with a single unnamed json or jsonb parameter in the schema cache'
  },
  data: null,
  count: null,
  status: 404,
  statusText: 'Not Found'
}

Am I missing something about how RPCs work?

16 Replies

Hello, I’m Allie!OP•3y ago

I tried reloading the Schema Cache as it suggests, but it didn't seem to do anything, at least in solving my problem

silentworks•3y ago

You are using CREATE PROCEDURE instead of CREATE FUNCTION, you need to use the latter. Basically procedures are called with a CALL keyword and this is not how .rpc works as it uses SELECT which is what functions are called with.

Hello, I’m Allie!OP•3y ago

Like this?

CREATE FUNCTION transact(id TEXT, created_at timestamptz, til TEXT, fra TEXT, beskrivelse TEXT, value float8)
RETURNS void
BEGIN
  INSERT INTO transaktioner VALUES (id, created_at, til, fra, beskrivelse, value);
  UPDATE kunder SET saldo = kunder.saldo - value WHERE id = fra;
  UPDATE kunder SET saldo = kunder.saldo + value WHERE id = til;
END:

CREATE FUNCTION transact(id TEXT, created_at timestamptz, til TEXT, fra TEXT, beskrivelse TEXT, value float8)
RETURNS void
BEGIN
  INSERT INTO transaktioner VALUES (id, created_at, til, fra, beskrivelse, value);
  UPDATE kunder SET saldo = kunder.saldo - value WHERE id = fra;
  UPDATE kunder SET saldo = kunder.saldo + value WHERE id = til;
END:

It seems to not like that I'm returning void. Is there a better way of defining that I don't want to return any data when successful?

silentworks•3y ago

What error are you getting? you can definitely return void when creating a function. Please always provide the error you get as it helps others to understand what might be going on.

Hello, I’m Allie!OP•3y ago

The error isn't very descriptive, though as to whether it was coming from void was a guess. Here's the error:

Failed to validate sql query: syntax error at or near "BEGIN"

Failed to validate sql query: syntax error at or near "BEGIN"

I assume it isn't that I need a semicolon after void? Sorry if the answer is really obvious, still trying to wrap my head around the basics in PSQL, and SQL in general.

silentworks•3y ago

But the error didn't mention void being an issue. Try

CREATE FUNCTION transact(id TEXT, created_at timestamptz, til TEXT, fra TEXT, beskrivelse TEXT, value float8)
RETURNS void
LANGUAGE plpgsql
AS
$$
BEGIN
  INSERT INTO transaktioner VALUES (id, created_at, til, fra, beskrivelse, value);
  UPDATE kunder SET saldo = kunder.saldo - value WHERE id = fra;
  UPDATE kunder SET saldo = kunder.saldo + value WHERE id = til;
END:
$$;

CREATE FUNCTION transact(id TEXT, created_at timestamptz, til TEXT, fra TEXT, beskrivelse TEXT, value float8)
RETURNS void
LANGUAGE plpgsql
AS
$$
BEGIN
  INSERT INTO transaktioner VALUES (id, created_at, til, fra, beskrivelse, value);
  UPDATE kunder SET saldo = kunder.saldo - value WHERE id = fra;
  UPDATE kunder SET saldo = kunder.saldo + value WHERE id = til;
END:
$$;

Hello, I’m Allie!OP•3y ago

Yeah, it was just a guess, as I've had issues with ending a line incorrectly causing a Syntax Error in the next line before. My mistake! Pushing an update now, let me see... Ok, so that got me a little farther. It complained about id being ambiguous, so I changed it to CREATE FUNCTION transact(transId TEXT, created_at timestamptz, til TEXT, fra TEXT, beskrivelse TEXT, value float8).... Other than that, it is seems to have gone through(no errors). I reloaded the schema cache, but now I'm getting the not-found error again...

{
  error: {
    code: 'PGRST202',
    details: null,
    hint: 'If a new function was created in the database with this name and parameters, try reloading the schema cache.',
    message: 'Could not find the public.transact(beskrivelse, created_at, fra, til, transId, value) function or the public.transact function with a single unnamed json or jsonb parameter in the schema cache'
  },
  data: null,
  count: null,
  status: 404,
  statusText: 'Not Found'
}

{
  error: {
    code: 'PGRST202',
    details: null,
    hint: 'If a new function was created in the database with this name and parameters, try reloading the schema cache.',
    message: 'Could not find the public.transact(beskrivelse, created_at, fra, til, transId, value) function or the public.transact function with a single unnamed json or jsonb parameter in the schema cache'
  },
  data: null,
  count: null,
  status: 404,
  statusText: 'Not Found'
}

This is when I try to call the Function from the JS RPC API.

silentworks•3y ago

Can you check this section inside of your Supabase Dashboard and let me know if you see the function there https://app.supabase.com/project/_/database/functions

Hello, I’m Allie!OP•3y ago

silentworks•3y ago

In the Dashboard it's showing transid and not transId

Hello, I’m Allie!OP•3y ago

Yeah, just realized, I've had that happen with table names too. Even if I give them a capital name, they can only be queried via the JS API when I lowercase them...

silentworks•3y ago

You should always use lowercase and underscore _ when creating table names and column names, this is the recommended way by Postgres.

Hello, I’m Allie!OP•3y ago

Is that a bug? Or is it intended to work that way?

silentworks•3y ago

Delete that function from the UI and then recreate it again.

Hello, I’m Allie!OP•3y ago

Ok, it works now! Thank you for your help!

silentworks•3y ago

No problem, I'm happy you got it all working.

Gaming

Programming

Postgres Procedure RPC with JS API

Did you find this page helpful?