Function made of transaction statement on PostgreSQL

Question

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).

score 1 · Answer 1 · 2020-08-06T12:55:53.687

1

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;

edited Aug 06 '20 at 12:55

answered Aug 06 '20 at 12:31

Thanks for the answer, I've copied the exact code now. So when the language is `sql`, I get `relation "recoyx_private.user" does not exist` (now I've re-tried it, resulting instead in `syntax error at or near "."` on the where clauses). According to [this](https://www.postgresql.org/docs/12/sql-createfunction.html) I've specified `language plpgsql` (which is valid), and then I now instead get `syntax error at or near "select"` at the outer select. – Aug 06 '20 at 13:05
If you get "relation does not exist" changing the function's language won't fix that (it would simply defer the error message to a later stage). Apparently you do not have such a table - simple as that (or don't have the privileges, or in a different database, or ...) – Aug 06 '20 at 13:09
I'm running the query [like this](https://github.com/recoyx/site/blob/master/database/schema/_exec.js#L11) and have environment configuration file [like this](https://github.com/recoyx/site/blob/master/environment-example.json#L4). – Aug 06 '20 at 13:39
This `create function` is preceded by other queries also... [schema.sql](https://github.com/recoyx/site/blob/master/database/schema/schema.sql). Maybe the role changes once the function body is entered? – Aug 06 '20 at 13:57

Function made of transaction statement on PostgreSQL

Update

1 Answers1