postgresql - 为什么我不能在这个 where 子句中使用 plsql 参数？

Question

我在 ( is_organizer) 下面有一个可以工作的函数，让我可以将此方法用作 Hasura 中的计算域。( ) 下面的功能is_chapter_member几乎相同，但不起作用。

作品

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;

破碎的

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;

尝试添加此函数时（不调用它，只需创建它）Postgres 给我以下错误：

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

为什么函数参数需要 where 子句？下面的表转储...

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

标签： postgresqlplpgsqlhasura

Answer 1

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.