PostgreSQL - computed column to return "is my" boolean

Question

I'm new to pg and dbs in general. I found a blog db boilerplate and I'm trying to add some computed columns to return Booleans for whether the currentPerson (the authenticated user) is the author of a post. Basically, a phantom column in the posts table that has a true or false for each post as to whether the currentPerson authored it.

This function returns the currentPerson:

SELECT *
FROM myschema.person
WHERE id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid

So, I would like to do something like below, but this is not correct. This appears as a mutation in graphiql (I'm using postgraphile to generate my schema).

Any tips on how to create an isMyPost boolean computed column in a posts table would be awesome.

CREATE OR REPLACE FUNCTION myschema.is_my_post(
    )
    RETURNS BOOLEAN AS 
$func$
BEGIN
  SELECT *
  FROM myschema.post
  WHERE author_id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid;
   IF FOUND THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END
$func$ LANGUAGE plpgsql;

Hi there. Could you also provide some sample data and if possible the exact expected result? I'm not really convinced you need a plpgsql function for that, but in case you must use it, perhaps this would give you some ideas `CREATE OR REPLACE FUNCTION is_my_post() RETURNS BOOLEAN AS $$ DECLARE res BOOLEAN := NULL; BEGIN SELECT * FROM post INTO res WHERE author_id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid; IF res IS NULL THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END $$ LANGUAGE plpgsql;` — Jim Jones, Apr 11 '21 at 13:07
I doubt in the need, too ... easily doable on resolver (postrgraphile computed) or even on client 'level' (apollo) — xadm, Apr 11 '21 at 13:28

score 0 · Answer 1 · answered Apr 14 '21 at 00:13

The answer ended up being this, thanks to @benjie @Graphile:

    CREATE OR REPLACE FUNCTION myschema.post_current_person_has_reacted(
        p myschema.post)
        RETURNS boolean
        LANGUAGE 'sql'
        COST 100
        STABLE PARALLEL UNSAFE
    AS $BODY$
    select exists(
    select 1
    from myschema.post_reactions pr
    where pr.post_id = p.id
    and pr.person_id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid
    )
    $BODY$;

PostgreSQL - computed column to return "is my" boolean

1 Answers1