CODEWITHSUNDEEP
node.jspostgresqlpostgraphile
user5066707
user5066707

Reputation:

Function made of transaction statement on PostgreSQL

I want to authenticate an user by its nickname (networkId) and before-hand hashed password, with an user public table (recoyx.user) and an user private table (recoyx_private.user). The function is based on browsing this PostGraphile tutorial (PostGraphile combines GraphQL and PostgreSQL).

create function recoyx.authenticate(
    network_id text,
    password_hash text
) returns recoyx.jwt_token
begin;
    set local id to (select (numeric_id) from recoyx.user where user.network_id = $1).numeric_id;
    select (numeric_id, password_hash)::recoyx.jwt_token
        from recoyx_private.user
        where user.numeric_id = id and user.password_hash = $2;
end;

The query runner is giving invalid syntax within this function overall, both at the part like select * from recoyx.table where table.field = value, transaction frames and the id binding. I took the query runner from this example which gives a short facility for initializing, querying and releasing the query runner for the PostgreSQL database (I got here through this postgraphile module API documentation).

When I eliminate this function from my query, it runs fine. As far as I've just seen the dot is valid, and the local assignment too. So is my syntax really wrong?

Update

Now this is my function:

create function recoyx.authenticate(
    network_id text,
    password_hash text
) returns recoyx.jwt_token
as
$body$
    select (numeric_id, password_hash)::recoyx.jwt_token
        from recoyx_private.user
        where   numeric_id = (select numeric_id from recoyx.user where network_id = $1)
            and password_hash = $2;
$body$
language sql
stable;

I'm getting undefined relations, but I'm connecting to the default root role that comes within my PostgreSQL installation (postgres role) as I run the create function query

I've put the project on GitHub. I'm running the query through npm run init-database. See environment-example.json (it specifies the conventional "postgres" role).

Upvotes: 0

Views: 176

Answers (1)

user330315
user330315

Reputation:

As documented in the manual the function body is passed as a string in Postgres (and the tutorial you linked to actually included the necessary as $$ ...$$ - you just didn't copy it). You also forgot to specify the function's language.

set local id is neither a valid variable assignment in PL/pgSQL nor in SQL (which doesn't have variables to begin with).

But you don't really need a variable to do what you want, your function can be implemented as a SQL function:

create function recoyx.authenticate(
    network_id text,
    password_hash text
) returns recoyx.jwt_token
as
$body$
  select (numeric_id, password_hash)::recoyx.jwt_token
  from recoyx_private.user
  where user.numeric_id = (select numeric_id 
                           from recoyx.user 
                           where network_id = $1)
    and user.password_hash = $2;
$body$
language sql
stable;

Upvotes: 1

Related Questions