问题[plpgsql](dba)

我目前在我的 PL/pgSQL 查询中遇到一个奇怪的问题，即在文本列中插入字符串，我将解释：

我有一个具有以下架构的表：

CREATE TABLE IF NOT EXISTS Loading_Info
            (
                ID SERIAL PRIMARY KEY ,
                Vendor_Loc VARCHAR(500),
                Dest_Loc VARCHAR(500)
);

该表将包含用于数据加载的文件位置。

我的插入查询如下：

INSERT INTO Loading_Info
    (
      ID,
      Vendor_Loc,
      Dest_Loc
    )
    VALUES
    (
      '1',
      's3://ind-uat-ics/uat/telco/',
      'C:\ind-uat-ics\uat\telco\'
    );

    COMMIT;

当我尝试在 SQL 工作台上执行此查询时，出现以下错误：

[Amazon](500310) Invalid operation: unterminated quoted string at or near "'C:\ind-uat-ics\uat\telco\',
        )"

您知道如何插入这些字符以被视为字符串吗？

我有一个复杂的触发功能，我希望将其分解为多个子功能。触发器将调用主函数，而主函数中将包含逻辑来决定调用哪个子函数。这是据我所知：

create table dbo.thing (thingid int);
create table dbo.thingtwo (thingid int);

create or replace function dbo.tf2(thingid int) returns void as $$
    insert into dbo.thingtwo values (thingid);
$$ language sql; 

create or replace function dbo.tf1() returns trigger as $thinginsert$
begin    
    perform dbo.tf2(new.thingid);
    return null;
end;
$thinginsert$ language plpgsql; 

create trigger thinginsert
after insert on dbo.thing
for each row execute procedure dbo.tf1();

问题是我想将整个传递给new.*第二个触发函数，而不仅仅是一些列。最终会有条件逻辑dbo.tf1()来决定是否去dbo.tf2()等等dbo.tf3()。

我怎样才能使这项工作？

我正在使用 Postgres 12.8。

我想为 postgresql 10 编写函数，以授予指定用户对所有数据库的只读访问权限（应该从超级用户运行）

但是我在数据库切换部分有困难。我尝试在游标内切换数据库，但出现我无法理解的错误。如何更正我的脚本？

上下文：执行时的 PL/pgSQL 函数 inline_code_block

DO $$
DECLARE query text;

cursor cursor
FOR
SELECT '\c '||datname ||';'  from pg_database;
BEGIN
OPEN cursor;
LOOP
    FETCH cursor INTO query;
    EXIT WHEN NOT FOUND;
    
    --raise notice 'Value: %',query;
    EXECUTE query;
END LOOP;
CLOSE cursor;
END $$;

我在 PLPGSQL 代码块中有一组时间戳：timestamp_array.

对于每个元素，timestamp_array我想获取元素本身 ( timestamp_element) 和表 () 中的一些列，该表my_table也具有时间戳列，但我不想获取表时间戳列本身，而是时间戳数组元素。条件是我只想为每个数组元素获取一个表行，即数组中的时间戳元素与表时间戳列之间最接近的匹配（到过去）。

我想要实现的是：

FOR EACH timestamp_element IN ARRAY timestamp_array
LOOP
  EXECUTE $EXE$
    SELECT my_table.some_other_columns...
    FROM my_table
    WHERE my_table.timestamp <= $1
    ORDER BY my_table.timestamp DESC
    LIMIT 1
  $EXE$
  USING timestamp_element
  INTO tmp_array;

  EXECUTE
    INSERT INTO temporary_table
    VALUES ($1, array_to_string($2, ', '))
  USING timestamp_element, tmp_array;
END LOOP;

我不知道前面的代码是否 100% 正确，但这只是为了更好地解释我想要实现的目标。显然，重点是只用一个查询来完成这项工作，而不是用一个查询数组元素。

如果我有一个实际案例my_table：

有一个timestamp_array：

[
  2020-02-15 12:00:00,
  2020-03-22 00:00:00,
  2020-06-14 17:00:00
]

我想要以下内容temporary_table，而不对每个数组元素使用一个独立的查询：

我试图在 PostgreSQL 的 format() 函数中转义一个 '%' 字符。该函数根据列列表替换列值，删除返回 (\n) 并修剪字符串。

CREATE OR REPLACE FUNCTION eliminar_retornos(text) RETURNS VOID
AS $$
        declare
            i text;
            fields text[] := ARRAY['direccion', 'localidad', 'calle', 'esq1', 'esq2', 'obs'];
        BEGIN
            FOREACH i IN ARRAY fields
            loop
                EXECUTE format(
                    'update %1$s set %2$s = trim(upper(replace(%2$s, E''\n'', '' '')))', $1, i)
                ;
            END LOOP;
        RAISE NOTICE 'Se actualizó la capa %', $1;
        END
        ;
$$ LANGUAGE plpgsql;

我想改变这个函数，只替换那些有返回的字符串，使用类似的东西；

'update %1$s set %2$s = trim(upper(replace(%2$s, E''\n'', '' ''))) where %2$s LIKE E''%\n%''', $1, i) 

问题是我不知道如何正确转义 format() 函数中“LIKE”的“%”。

很抱歉发布了一些不可复制的东西，我的知识仅限于实现这一点。

您能否提供在 PostgreSQL 函数中使用用户定义异常的语法？

假设我想处理以下用户定义的异常。

SQL Error [22023]: ERROR: password is too short.

有多个 SQLSTATE 错误代码，但无法找到此错误的 SQLSTATE 代码。我在上面使用的是 22023 但没有解决。

我们有以下代码，我们能够管理唯一的违规相关异常，但无法管理“密码太短”。你能帮我语法吗？

代码：

begin
EXECUTE 'ALTER USER ' || $1 || ' WITH PASSWORD '''|| $2||'''' ;
        EXCEPTION WHEN "Password is too short" 
        THEN RAISE DETAIL 'Please check your password';
        
INSERT INTO pwdhistory (usename,password,changed_on) values($1,md5($2),now());
        EXCEPTION WHEN unique_violation 
        THEN RAISE DETAIL 'Password already used earlier. Please try again with another password.';

end;

是否可以在 Postgres 13 中定义递归复合类型？

create type "t" as (
  "a" int,
  "b" t
);

我知道我可以使用具有父子关系的表。唯一的缺点是我需要查询每个嵌套级别，而不是直接访问嵌套数据。

我也知道我可以使用jsonb，但我不确定它是否可以直接访问嵌套数据而不查询每个嵌套级别？

仅使用 PL/pgSQL 的其他替代方案是什么？

我需要分别访问每个嵌套级别。

我正在尝试创建一个触发器，以防止列更新如下：

如果列值为“A”，则无法更新为“B”，如果列值为“B”，则无法更新为“A”。

CREATE OR REPLACE FUNCTION cancelled_order()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$BEGIN
    IF OLD.status = 'Cancelled' THEN NEW.status != 'Accomplished';
    END IF;
    IF OLD.status = 'Accomplished' THEN NEW.status != 'Cancelled';
    END IF;
    RETURN NEW;
END;
$function$;

CREATE TRIGGER No_cancelled_acomplished
    BEFORE UPDATE OF status
    ON clinical.tb_orders
    EXECUTE PROCEDURE cancelled_order();

出现的错误是：

Sintaxis error: != 

有什么想法，为什么？

我有一个函数，我在其中指定表和列，但我需要一个可以用于任何表和列的聚合以获得类似的输出

create function min_max_val()
returns text
 LANGUAGE plpgsql
 AS $$
  declare
  mmv text;
  begin
  select format('%s - %s',min(id),max(id) )min_max_vals into mmv from towns;
  return mmv;
 end;
$$ ;

    min_max_vals
---------------------
 1000001 - 11000000

我编写了以下函数来从我作为参数提供的表和列中查询数据：

create or replace function field_summaries(gap interval , _tbl anyelement, _col text)                                
 returns SETOF anyelement as 
 $func$   
 BEGIN
 RETURN QUERY EXECUTE
 'select                                                                                                                       
 time_bucket(' || gap || ', time)::text as hour,                                                                                      
 avg(' || _col ||'),                                                                                                          
 min(' || _col ||'),                                                                                                          
 max(' || _col ||')                                                                                                         
 from ' || pg_typeof(_tbl) || ' d                                                                                                         
 where d.device_id in (                                                                                                       
     select device_id from sensors)                                                                                           
     group by hour';
 END
 $func$ language sql stable;  

问题是当调用这样的函数时：

select field_summaries('5minutes', NULL:: m_13, 'temperature');   

我收到以下错误：

ERROR:  syntax error at or near ":"  
LINE 2:  time_bucket(00:05:00, time)::text as hour,                 ...                                                                           
                       ^                                                                                                   
QUERY:  select                                                                                                                                                                                                                                         
time_bucket(00:05:00, time)::text as hour,                                                                                                                                                                                                            
avg(temperature),                                                                                                                                                                                                                                     
min(temperature),                                                                                                                                                                                                                                     
max(temperature)                                                                                                           
from m_13 d                                                                                                                
where d.device_id in (                                                                                                                                                                                                                                    
select device_id from sensors)                                                                                                                                                                                                                        
group by hour                                                                                                         
CONTEXT:  PL/pgSQL function field_summaries(interval,anyelement,text) line 3 at RETURN QUERY

有谁知道这可能是什么？