Postgraphile: How to solve "ambiguous column" warning in PostgreSQL function?

[benbonnet]

I'm submitting a ...

• [ ] bug report
• [ ] feature request
• [x] question (Please suggest another title, it could be wrong, i based it on the intuition i have about the issue)

PostGraphile version: latest

Minimal SQL file that can be loaded into a clean database:

create function sc.authenticate(_email text) returns sc.jwt_token as $$
    declare
      account sc.users;
    BEGIN
      IF EXISTS (select 1 from schema.table where sc.users.email = _email) THEN
        select a.* into account
        from sc.users as a
        where a.email = $1;
        return ('postgraphql_user_public', account.id)::sc.jwt_token;
      ELSE
        insert into sc.users(email)
        values ( _email)
        returning * into account;
        return ('postgraphql_user_public', account.id)::sc.jwt_token;
      END IF;
    END;
    $$ language plpgsql strict security definer;

Unsure about it being the best way to do so, but using it in postgres sql console (without going through postgraphile), it works.

I've underscored the parameter, my column table is standard, email. Without underscoring (or simply changing it), postgres complains about ambiguity in the IF EXISTS parts (more specifically, if i keep sc.authenticate(email text), then the IF EXISTS (select 1 from schema.table where sc.users.email = email) THEN breaks, being ambiguous)

Steps to reproduce:

run a mutation

mutation Authenticate($email: String!) {
  authenticate(input: { email: $email } ) { jwtToken }
}

Current behavior:

posgraphile returns an error :

{ message: 'Field AuthenticateInput._email of required type String! was not provided.',
       locations: [Array] }

Expected behavior:

Not getting an error. I guess this might just be about aliasing the parameter (I searched for it, to do it in the pg function, without success), or to specify it somehow else in the mutation; but I'm a bit lost about how to do it correctly

Thx a lot for your help

[benjie]

You can use the function name to disambiguate the field in postgres, e.g.:

create function sc.authenticate(email text) returns sc.jwt_token as $$
    declare
      account sc.users;
    BEGIN
      IF EXISTS (select 1 from schema.table where sc.users.email = authenticate.email) THEN
        select a.* into account
        from sc.users as a
        where a.email = authenticate.email;
        return ('postgraphql_user_public', account.id)::sc.jwt_token;
      ELSE
        insert into sc.users(email)
        values ( authenticate.email)
        returning * into account;
        return ('postgraphql_user_public', account.id)::sc.jwt_token;
      END IF;
    END;
    $$ language plpgsql strict security definer;

[benbonnet]

I guess this one has more to do with postgres knowledge rather than postgraphile itself

(ps: positively amazed by how fast your answer; Thanks a lot for such support!)

[benbonnet]

Not related but if it can be asked here; I'm using uuid's, and the following :

mutation Authenticate($email: String!) {
  authenticate(input: { email: $email } ) {
    jwtToken
    query {
      currentUser {
        id
      }
    }
  }
}

returns :

{
  "errors": [
    {
      "message": "invalid input syntax for uuid: \"0\"",
      "locations": [
        {
          "line": 20,
          "column": 7
        }
      ],
      "path": [
        "authenticate",
        "query",
        "currentUser"
      ]
    }
  ],
  "data": { ... // the rest is okay }
}

Is there something to specify somewhere to have it working ? Should the id (uuid) be returned by the pg function ?

[benjie]

Could you open a separate issue for this and massively expand on it? I'm not sure what's a UUID (id?) and where the "0" in the error message is coming from?

[benbonnet]

ok sorry for that, extracted it to another one; hope I've specified enough

[hugo]

You can also refer to arguments using the $n syntax:

create function sc.authenticate(email text) returns sc.jwt_token as $$
with account as (
  insert into sc.users (email) values ($1)
  on conflict (email) do update
  set email = excluded.email
  returning *
)
select ('postgraphql_user_public', account.id)::sc.jwt_token;
$$ language sql strict security definer;

[chadfurman]

Using $n might be confusing, I have been giving my args _prefixes so like _email etc -- this works alright. I really like the function prefix though, I did not know about that :)

[hugo]

To counter, $n is the same syntax as you would use in a prepared statement.

[benjie]

[semi-automated message] To keep things manageable I'm going to close this issue as I think it's solved; but if not or you require further help please re-open it.