Ced's questions

Estou tentando usar ON CONFLICTem duas colunas onde uma pode ser null. Infelizmente, com o índice parcial, não consigo fazê-lo.

create table tbl( col1 int, col2 int, col3 boolean);

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NOT NULL; -- NOT NULL

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NULL;     -- NULL

INSERT INTO tbl(col1, col2)
values(1, 3) 
ON CONFLICT(col1, col2) DO UPDATE SET col3 = true;

ERRO: não há restrição exclusiva ou de exclusão que corresponda à especificação ON CONFLICT

Tenho 2 gatilhos. triggerAe triggerBque atua sobre mudanças em tableAe tableBrespectivamente. Dentro de ambos os gatilhos, quero usar a mesma função que usa as variáveis OLD​​&NEW . Eu não sei como fazer isso porque me lança o erro:

OLD hasn't been assigned yet.
The tuple structure of a not-yet-assigned record is indeterminate.

Então eu tentei atribuir null a uma variável para contornar isso e é:

old_v hasn't been assigned yet.

gatilho 1 e 2 (esta parte é comum em ambos os gatilhos):

DECLARE 
    added added_scores;
    old_v record;
    new_v record;
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            old_v = null;
            new_v = NEW;
        WHEN 'UPDATE' THEN
            old_v = OLD;
            new_v = NEW;
        WHEN 'DELETE' THEN
            old_v = OLD;
            new_v = null;
    END CASE;

    select _i_compute_added_scores(TG_OP, old_v, new_v) into added;
        -- some computation, return result

função:

CREATE OR REPLACE FUNCTION public._i_compute_added_scores(
    tg_op_p text,
    old_p anyelement,
    new_p anyelement)
  RETURNS added_scores AS
$BODY$

DECLARE
    new_score integer;
    old_score integer;
    res added_scores;
BEGIN
    CASE TG_OP_p
        WHEN 'INSERT' THEN
            new_score := new_p.score;
            old_score := 0;
        WHEN 'DELETE' THEN
            new_score := 0;
            old_score := old_p.score;
        WHEN 'UPDATE' THEN
            new_score := new_p.score;
            old_score := old_p.score;
    END CASE;

-- some more computation and return

Alguém me disse que usar entrada de texto para nomes de coluna e formatá-la, como faço abaixo, raramente é uma boa ideia. Quando perguntei por que, no entanto, uma resposta não foi dada. Isso foi no postgresql IRC, e esses caras parecem saber das coisas. Então eu gostaria de saber por que não é aconselhável? Estou principalmente me perguntando se isso abre a porta para injeção de sql.

create or replace function getItemsOrderBy(order_by_p text)
RETURNS TABLE (id int) AS $$


BEGIN

    return query EXECUTE format('
    SELECT id
    FROM items 
    ORDER BY %s', order_by_p) ;

END;

Ele também disse para usar executecom usingem vez disso, então qual é a diferença entre isso:

return query EXECUTE format('
SELECT id
FROM items 
ORDER BY %s', order_by_p) ;

e isto :

return query EXECUTE '
SELECT id
FROM items 
ORDER BY $1' USING order_by_p ;

Minha função é mais complexa do que a acima - a parte do formato é apenas parte dela. Eu tenho a opção de criar uma função que pode lidar com vários casos (para pedidos) ou criar vários deles para lidar com todos os pedidos. Eu senti que fazer apenas um era mais prático. Não ter nenhuma função não é uma opção.

Na verdade, estou usando pg-promise, mas fiquei com a impressão de que, como estou fazendo muitas idas e vindas entre o back-end e o banco de dados (enviar algo, aguardar resposta, calcular outra coisa, enviar novamente ..) eu deveria ir com função e deixe tudo acontecer de uma vez.

Ao usar o relacionamento de muitos para muitos, o resultado é dividido em várias linhas. O que eu gostaria de fazer é converter o lado direito de uma junção em uma matriz para que o resultado seja uma linha.

Exemplo com 3 tabelas:

CREATE TABLE items (
  id    serial primary key,
  title text
);

CREATE TABLE tags (
  id    serial primary key,
  title text
);

CREATE TABLE items_tags (
  item_id int references items(id),
  tag_id  int references tags(id),
  primary key (item_id, tag_id)
);

Ao selecionar itens com suas tags, posso fazer assim:

SELECT i.id, i.title, i.title
FROM items i
INNER JOIN items_tags it
ON it.item_id = i.id
INNER JOIN tags t
ON t.id = it.tag_id;

E o resultado será:

(1, "item n1", "sport")
(1, "item n1", "soccer")
(2, "item n2", "adventure")
(2, "item n2", "mountain climbing")
(2, "item n2", "sport")
(2, "item n2", "nature")

O que eu gostaria de ter é isso:

(1, "item n1", ["sport", "soccer"])
(2, "item n2", ["adventure", "mountain climbing", "sport" , "nature"])