Return result of CASE with Posgres SQL function

Question

I am trying to write a simple SQL function that will be used as a computed field in Hasura.io platform.

CREATE OR REPLACE FUNCTION calling_mode(calling_row calling, hasura_session json)
RETURNS text AS $$
  SELECT CASE
    WHEN calling_row.seekerUserId = hasura_session ->> 'x-hasura-user-id' THEN 'seeker'
    WHEN calling_row.partnerUserId = hasura_session ->> 'x-hasura-user-id' THEN 'partner'
    ELSE 'none'
  END
$$ LANGUAGE sql STABLE;

This gives me an error missing FROM-clause entry for table "calling_row".

I tried looking through several docs, but it doesn't make any sense to me.

Oto Shavadze · Accepted Answer · 2020-06-28 10:36:47Z

1

Probably that causes upper case letters, so if your original column name is seekerUserId, then into your function postgres converts it as seekeruserid, and that column is not found.

Try column names into double quotes, like:

CREATE OR REPLACE FUNCTION calling_mode(calling_row calling, hasura_session json)
RETURNS text AS $$
  SELECT CASE
    WHEN calling_row."seekerUserId" = hasura_session ->> 'x-hasura-user-id' THEN 'seeker'
    WHEN calling_row."partnerUserId" = hasura_session ->> 'x-hasura-user-id' THEN 'partner'
    ELSE 'none'
  END
$$ LANGUAGE sql STABLE;

answered Jun 28, 2020 at 10:36

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Return result of CASE with Posgres SQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related