Perguntas[plpgsql](dba)

Atualmente estou enfrentando um problema estranho na minha consulta PL/pgSQL para inserir string na coluna de texto, vou explicar:

Tenho uma tabela com o seguinte esquema:

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

Esta tabela conterá a localização do arquivo para carregamento de dados.

Minha consulta de inserção é a seguinte:

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

    COMMIT;

Quando estou tentando executar esta consulta no SQL workbench, recebi o seguinte erro:

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

Você tem alguma ideia de como inserir esses caracteres para serem considerados como string?

Eu tenho uma função de gatilho complexa que desejo dividir em várias subfunções. O gatilho chamaria a função master e a função master teria lógica para decidir qual subfunção chamar. Isso é até onde eu cheguei:

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();

O problema é que eu quero passar tudo new.*para a segunda função de gatilho, não apenas algumas colunas. Em última análise, haveria lógica condicional dbo.tf1()para decidir se ir dbo.tf2()ou dbo.tf3()etc.

Como posso fazer isso funcionar?

Estou usando o Postgres 12.8.

Eu quero escrever a função para o postgresql 10 para conceder acesso somente leitura a todos os bancos de dados para o usuário especificado (consideravelmente deve ser executado a partir do superusuário)

Mas tenho dificuldades na parte de troca de banco de dados. Eu tento alternar o banco de dados dentro de um cursor, mas recebo um erro que não consigo entender. Como posso corrigir meu script?

CONTEXTO: função PL/pgSQL inline_code_block em EXECUTE

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

Eu tenho uma matriz de carimbos de data/hora em um bloco de código PLPGSQL: timestamp_array.

Para cada elemento de timestamp_arrayeu quero pegar o próprio elemento ( timestamp_element) e algumas colunas de uma tabela ( my_table) que também tem uma coluna timestamp, mas não quero pegar a coluna timestamp em si, mas sim o elemento timestamp array. A condição é que eu só queira obter uma linha da tabela para cada elemento da matriz, a correspondência mais próxima (no passado) entre os elementos de carimbo de data/hora na matriz e a coluna de carimbo de data/hora da tabela.

O que eu quero alcançar é algo como:

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;

Não sei se o código anterior está 100% correto, mas é apenas para tentar explicar melhor o que quero alcançar. E, obviamente, o objetivo seria fazer o trabalho com apenas uma consulta, em vez de uma consulta por elemento de matriz.

Para colocar como um caso prático, se eu tiver um my_table:

Com um timestamp_array:

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

Eu gostaria do seguinte temporary_tablesem usar uma consulta independente para cada elemento da matriz:

Estou tentando escapar de um caractere '%' dentro de uma função format() no PostgreSQL. A função substitui os valores das colunas com base em uma lista de colunas, excluindo retornos (\n) e aparando as strings.

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;

Eu quero mudar essa função para substituir apenas aquelas strings que tem retornos, usando algo como;

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

O problema é que não sei como escapar corretamente o '%' para o 'LIKE' dentro da função format().

Desculpe por postar algo não reproduzível, meu conhecimento é limitado para conseguir isso.

Você pode fornecer a sintaxe para usar a exceção definida pelo usuário na função PostgreSQL?

Suponha que eu queira lidar com a exceção definida pelo usuário.

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

Existem vários códigos de erro SQLSTATE, mas não é possível encontrar qual é o código SQLSTATE para este erro. Usei acima que é 22023 mas não resolveu.

Temos abaixo o código que podemos gerenciar a exceção relacionada à violação exclusiva, mas não podemos gerenciar "a senha é muito curta". poderia me ajudar com a sintaxe?

Código:

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;

É possível definir tipos compostos recursivos no Postgres 13?

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

Estou ciente de que posso usar uma tabela com um relacionamento pai-filho. A única desvantagem é que precisarei consultar cada nível aninhado em vez de acessar diretamente os dados aninhados.

Também estou ciente de que posso usar jsonb, mas não tenho certeza se ele pode acessar os dados aninhados diretamente sem consultar cada nível aninhado?

Quais são as outras alternativas usando apenas PL/pgSQL?

Eu preciso acessar cada nível aninhado separadamente.

Estou tentando criar um gatilho que impeça que uma coluna seja atualizada da seguinte forma:

Se o valor da coluna for "A", ele não poderá ser atualizado para "B" e se o valor da coluna for "B", ele não poderá ser atualizado para "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();

O erro que sai é:

Sintaxis error: != 

Alguma ideia, por quê?

Eu tenho uma função em que estou especificando a tabela e a coluna, mas preciso de um agregado que possa funcionar para qualquer tabela e coluna para uma saída semelhante

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

Eu escrevi a seguinte função para consultar dados de tabelas e colunas que dou como argumentos:

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;  

O problema é que ao chamar a função assim:

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

Eu recebo o seguinte erro:

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

Alguém tem ideia do que pode ser isso?