postgresql - 在 plpgsql 函数中对值使用单引号

Question

我有一个接受 4 个参数的 postgresql 函数

CREATE OR REPLACE FUNCTION public.manual_table_parsing_monthly_custom_edits(
schema_name text,
main_permit text,
manual_table text,
permit_num text)
RETURNS void AS
$BODY$
DECLARE
    e record;
    load_move_count double precision;
BEGIN
  EXECUTE format('Insert into %I.%I(permit_date,permit_number,year,month_number,
    report_permit_date,job_category,address,legal_description,neighbourhood,neighbourhood_number,
    job_description,work_type,floor_area,construction_value,zoning,units_added,
    latitude,longitude,location,count,building_type,building_type_number,filter,community,
    groups,plan,block,lot,unit_id,dwelling,condo_fh,developer,builder,unit_address,construction_type,geom)
            select permit_date,permit_number,year,month_number,
    report_permit_date,job_category,address,legal_description,neighbourhood,neighbourhood_number,
    job_description,work_type,floor_area,construction_value,zoning,units_added,
    latitude,longitude,location,count,building_type,building_type_number,filter,community,
    groups,plan,block,lot,unit_id,dwelling,condo_fh,developer,builder,unit_address,construction_type,geom
from %I.%I
where flag like ''move'' or flag like ''load'' and permit_number 
=%I',schema_name,main_permit,schema_name,manual_table,permit_number);

在 where 语句中使用的函数 permit_number 中的最后一个参数引发错误 no column 'yyyy'。这是因为 %I 在双引号内取值。我应该如何编辑以使值在单引号内

Answer 1

根据 documentiaion %I 应用于标识符，%L 应用于文字值。

还应该使用 $$ 引号，以便于阅读您的函数...