路过

在PostgreSQL 13中有一个自定义枚举类型，定义为：

CREATE TYPE public.dbscale AS ENUM
    ('A', 'B', 'C', 'D', 'Z');

尝试使用下面sub-select中的dbscale枚举时，我得到一个
error: 42703 column “dbscale”\ does not exist.

CREATE OR REPLACE FUNCTION public.xfertodecibellog(
    jobid integer,
    instid integer)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    INSERT INTO decibellog(jobid, 
                           instrumentid,
                           readingdate,
                           readingtime,
                           decibelreading,
                           dbweighting)
    SELECT @jobid,
           @instid,
           CAST(dbdate AS date),
           CAST(dbtime AS time),
           CAST(dbvalue AS double precision),
           CASE dbunit 
                WHEN 'dB(B)' THEN (dbscale).B
                WHEN 'dB(C)' THEN (dbscale).C
                WHEN 'dB(D)' THEN (dbscale).D
                WHEN 'dB(Z)' THEN (dbscale).Z
                ELSE (dbscale).A
           END
    FROM stagingdecibellog;
END;
$BODY$;

mark

路过

排队学习

mark

使用这个语句

alter type XXX add value yyy...

再赠送一个问题，该语句属于DDL，应用层如果要防注入可以使用函数封装

create type currency as enum ('USD','EUR','GBP');

CREATE OR REPLACE FUNCTION public.f_enum_add_val(enmu_val character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
    execute format('alter type currency add value %L ',enmu_val);
end;
$function$;