postgresql - 带条件语句的 plpgsql 函数访问列名

Question

我正在尝试在 plpgsql 函数中创建条件语句，它将通过 if 语句仅过滤出我想要的记录。我现在只是在测试，但这是我的表结构:

CREATE TABLE addresses
(
  gid serial NOT NULL,
  housenum character varying(30),
  prefix character varying(10),
  name character varying(100),
  type character varying(16)
)

这是我的功能:

CREATE OR REPLACE FUNCTION "geomCheck".getAllFoo() RETURNS SETOF 
addresses AS
    $BODY$
    DECLARE
        r addresses%rowtype;
    BEGIN
        FOR r IN SELECT * FROM addresses
        WHERE gid > 0
        LOOP
        if name = 'BRIE' then
         RETURN NEXT r;
        end if;
        END LOOP;
        RETURN;
    END
    $BODY$
    LANGUAGE 'plpgsql' ;

但是当我去调用这个函数时，我得到了这个错误:

ERROR:  column "name" does not exist
LINE 1: SELECT name = 'BRIE'
               ^
QUERY:  SELECT name = 'BRIE'
CONTEXT:  PL/pgSQL function "geomCheck".getallfoo() line 8 at IF
********** Error **********

ERROR: column "name" does not exist
SQL state: 42703
Context: PL/pgSQL function "geomCheck".getallfoo() line 8 at IF

如何检查 if 语句中的 name = 'BRIE'？

Answer 1

您需要在名称前加上r:

BEGIN
    FOR r IN SELECT * FROM addresses
    WHERE gid > 0
    LOOP
    if r.name = 'BRIE' then    -- instead of "name" => "r.name"
     RETURN NEXT r;
    end if;
    END LOOP;
    RETURN;
END