Why can't I use a plsql argument in this where clause?

Question

I have a function below (is_organizer) that works, and lets me use this method as a computed field in Hasura. The function below (is_chapter_member) which is almost identical, doesn't work.

WORKS

CREATE OR REPLACE FUNCTION is_organizer(event_row events, hasura_session json)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1
    FROM event_organizers o
    WHERE
      o.user_id::text = hasura_session->>'x-hasura-user-id'
      AND
      (event_row.id = o.event_id OR event_row.event_template_id = o.event_template_id)
  );
$$ LANGUAGE SQL STRICT IMMUTABLE;

BROKEN

CREATE OR REPLACE FUNCTION is_chapter_member(c chapters, hasura_session json)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1
    FROM chapter_members m
    WHERE
      m.user_id::text = hasura_session->>'x-hasura-user-id'
      AND
      c.chapter_id = m.chapter_id
  );
$$ LANGUAGE SQL STRICT IMMUTABLE;

When attempting to add this function (not call it, just create it) Postgres gives me the following error:

ERROR:  missing FROM-clause entry for table "c"
LINE 9:       c.chapter_id = m.chapter_id

Why would a function param need a where clause? Table dumps below...

                                         Table "public.chapters"
     Column      |           Type           | Collation | Nullable |               Default                
-----------------+--------------------------+-----------+----------+--------------------------------------
 id              | integer                  |           | not null | nextval('chapters_id_seq'::regclass)
 title           | text                     |           | not null | 
 slug            | text                     |           | not null | 
 description     | jsonb                    |           |          | 
 avatar_url      | text                     |           |          | 
 photo_url       | text                     |           |          | 
 region          | text                     |           |          | 
 maps_api_result | jsonb                    |           |          | 
 lat             | numeric(11,8)            |           |          | 
 lng             | numeric(11,8)            |           |          | 
 created_at      | timestamp with time zone |           | not null | now()
 updated_at      | timestamp with time zone |           | not null | now()
 deleted_at      | timestamp with time zone |           |          | 

                     Table "public.chapter_members"
   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 user_id    | integer                  |           | not null | 
 chapter_id | integer                  |           | not null | 
 created_at | timestamp with time zone |           | not null | now()
 updated_at | timestamp with time zone |           | not null | now()


                                                   Table "public.events"
      Column       |            Type             | Collation | Nullable |                      Default                      
-------------------+-----------------------------+-----------+----------+---------------------------------------------------
 id                | integer                     |           | not null | nextval('events_id_seq'::regclass)
 event_template_id | integer                     |           | not null | 
 venue_id          | integer                     |           |          | 
 starts_at         | timestamp without time zone |           | not null | 
 duration          | interval                    |           | not null | 
 title             | text                        |           |          | 
 slug              | text                        |           |          | 
 description       | text                        |           |          | 
 photo_url         | text                        |           |          | 
 created_at        | timestamp without time zone |           | not null | now()
 updated_at        | timestamp without time zone |           | not null | now()
 deleted_at        | timestamp without time zone |           |          | 
 ends_at           | timestamp without time zone |           |          | generated always as (starts_at + duration) stored


                                  Table "public.event_organizers"
      Column       |  Type   | Collation | Nullable |                   Default                    
-------------------+---------+-----------+----------+----------------------------------------------
 id                | integer |           | not null | nextval('event_organizers_id_seq'::regclass)
 user_id           | integer |           | not null | 
 event_id          | integer |           |          | 
 event_template_id | integer |           |          |

try `(c).chapters` - there can be problems in the parser since . is defined as a . reference by the SQL standards. Record/row types aren't supported by all RDBMS. — Richard Huxton, Oct 20 '20 at 15:03
@RichardHuxton you were on the right track. Using `(c).chapter_id` raised the next issue, which was a missing field, final answer below. — Trey Stout, Oct 20 '20 at 15:13

score 0 · Answer 1 · answered Oct 20 '20 at 15:15

This turned out to be using an incorrect column name in the broken function. chapter_id should have just been id on the c argument. I took Richard's prompt and tried putting parens around the arg like (c).chapter_id. This then correctly told me that chapter_id doesn't exist, and allowed me to fix the issue.

Why can't I use a plsql argument in this where clause?

WORKS

BROKEN

1 Answers1