Yanick Rochon's questions

É possível retornar as chaves de um objeto JSON como uma matriz de valores no PostgreSQL?

Em JavaScript, isso seria simplesmente Object.keys(obj), que retorna uma matriz de strings.

Por exemplo, se eu tiver uma tabela assim:

tbl_items
---------
id bigserial NOT NULL
obj json NOT NULL

E se houver uma linha como esta:

id      obj
-----   -------------------------
123     '{"foo":1,"bar":2}'

Como posso ter uma consulta para retornar:

id      keys
-----   ------------------
123     '{"foo","bar"}'

O aplicativo no qual estou trabalhando exibe algumas tarefas com base no status e na data (nessa ordem). No entanto, para um determinado status, a condição de classificação deve ser invertida. Por exemplo, uma tabela que se parece com isso:

 id    | status   | planned_date
-------+----------+--------------
 1     | inactive | 2015-03-12
 2     | active   | 2015-03-13
 3     | inactive | 2015-03-13
 4     | inactive | 2015-03-14
 5     | active   | 2015-03-12
 6     | active   | 2015-03-16
 7     | active   | 2015-03-17

Deve ser devolvido como

 id    | status   | planned_date
-------+----------+--------------
 5     | active   | 2015-03-12
 2     | active   | 2015-03-13
 6     | active   | 2015-03-16
 7     | active   | 2015-03-17
 4     | inactive | 2015-03-14
 3     | inactive | 2015-03-13
 1     | inactive | 2015-03-12

Nota : Para que este exemplo seja simples, apenas dois status são usados, mas esta é uma enumgeração de 6 valores diferentes para este campo; new, pending, active, inactive, cancelled, completed. Além disso, existem outros campos que precisam ser classificados: planned_date, e priority(um valor numérico de -3a 3, 0sendo "normal"). Estou mencionando isso apenas como um FYI, pois não acredito que seja muito relevante e que a ideia geral possa ser entendida com o exemplo simples acima.

Para ordenar pelo statuscampo (an enum) eu simplesmente executo

SELECT id, status, planned_date
FROM tbl_tasks
WHERE ...
ORDER BY status ASC;

Agora, como classifico o planned_datecampo ( ASCpara activestatus e DESCpara inactivestatus)?

Estou criando uma view para uma determinada tabela, onde as linhas possuem timestampcampos ( planned_ts). A exibição precisa ser classificada de acordo com esses registros de data e hora, mas como o aplicativo está interessado apenas na data e não na hora, o resultado às vezes parece aleatório, especialmente ao classificar vários campos.

A pergunta que tenho é, se eu tiver essa consulta (por exemplo):

SELECT *
  FROM tblFoo
 WHERE ...
 ORDER BY planned_ts ASC NULLS LAST, priority ASC

( NOTA : onde priorityé um integercampo, menor significa maior prioridade.)

Como posso alterar a cláusula do pedido, para que não considere o tempo, como se todos os timestamps estivessem em 00:00:00.000? Estou ciente de que posso usar funções na cláusula sort, mas não tenho certeza de como fazer isso no PostgreSQL.

Para um projeto, criei uma tabela com uma coluna price MONEY NOT NULLcoluna. E eu pensei que iria lidar com decimais corretamente, ao contrário de um número flutuante (ou seja, problemas de arredondamento IEEE), mas acabei tendo valores como $9.94(string) sendo retornados do banco de dados, em vez de 9.94(numérico). Ter que manipular manualmente o valor do campo removendo o cifrão é bobagem; existe uma maneira de ter uma MONEYcoluna sem um sinal de moeda?

Se não, qual é a melhor substituição de tipo de dados para lidar com valores de moeda?

Eu tenho um VIEWonde estou tentando criar um script de evolução, para que eu possa adicionar uma coluna a ele. Essa parte funciona bem; coluna adicionada muito bem. No entanto, o inverso não funciona; remover essa última coluna adicionada falha com uma ERROR: cannot drop columns from viewmensagem. O problema é que essa visão em particular tem muitas referências, tanto de quanto para, portanto, não posso apenas DROP CASCADEa maldita coisa!

Existe uma razão pela qual não consigo remover uma coluna recém-adicionada de um determinado VIEW? Então, o que posso fazer para realizar essa tarefa?

(Nota: as circunstâncias, aqui, são o que são, mas posso muito bem ver uma situação semelhante, também conhecida como descartar uma coluna de uma exibição, em muitos outros casos.)

Preciso adicionar um gatilho após inserir ou atualizar em uma tabela para que, onde alguma linha for atualizada e uma determinada condição for atendida, alguma outra tabela precise ser atualizada. No entanto, essa última instrução de atualização pode gerar um erro (a atualização cria um índice duplicado na tabela), então preciso adotar uma abordagem diferente, o que significa que preciso excluir em vez de atualizar.

A função, até agora, é esta:

CREATE OR REPLACE FUNCTION prod_profiles_check_grouped_item()
    RETURNS trigger AS $BODY$ 
DECLARE found_group bigint; 
BEGIN 
    SELECT group_id INTO found_group 
      FROM inv_items_group_members 
     WHERE item_id = NEW.item_id; 
     IF found_group IS NOT NULL THEN 
        UPDATE public.prod_profiles_steps_items 
           SET item_id = found_group 
         WHERE item_id = NEW.item_id;

        -- TODO : on error for the last update, this should get executed:
        -- DELETE FROM public.prod_profiles_steps_items 
        -- WHERE item_id = NEW.item_id;

    END IF;

    RETURN NEW;
END; $BODY$

Como isso pode ser feito? Tudo o que li em todos os lugares são RAISE. O que é irrelevante aqui.

(Desculpe o título vago, se alguém quiser reeditar, fique à vontade!)

Eu tenho essas tabelas e exibições para um módulo de inventário de fábrica:

+----------------------------+    +--------------------------+
| inv_items                  |    | inv_items_stock          |
+----------------------------+    +--------------------------+
| id bigserial (PK)          |    | item_id (FK) (inv_items) |
| name character varying(32) |    | qty integer              |
| is_group boolean           |    | ...                      |
| ...                        |    +--------------------------+
+----------------------------+ 

+---------------------------+     +---------------------------------+
| inv_items_group           |     | inv_items_production            |
+---------------------------+     +---------------------------------+
| group_id (FK) (inv_items) |     | item_id (FK) (inv_items)        |
| item_id (FK) (inv_items)  |     | unit_name character varying(16) |
+---------------------------+     | unit_qty number(12,4)           |
                                  +---------------------------------+

+----------------------------------+   +------------------------------+
| inv_items_vw                     |   | inv_items_groups_vw          |      
+----------------------------------+   +------------------------------+
| id (inv_items)                   |   | id (inv_items)               |
| name (inv_items)                 |   | name (inv_items)             |
| qty (inv_items_stock)            |   | ...                          |
| unit_name (inv_items_production) |   | items (json[]) (inv_item_vw) |
| unit_qty (inv_items_production)  |   +------------------------------+
| ...                              |
+----------------------------------+

Agora, para outra visualização, preciso buscar a 1linha de um dado item_ide retornar de inv_items_vwou inv_items_groups_vw, algo como

SELECT * 
  FROM (SELECT id, name, qty, unit_name, unit_qty, ..., null
          FROM inv_items_vw
         UNION
        SELECT id, name, null, null, null, ..., items
          FROM inv_items_groups_vw) item
 WHERE item.id = @id

Mas essa última consulta retorna ERROR: could not identify an equality operator for type json.

Entendo o erro e por que recebo isso, no entanto, preciso dessa consulta :)

Como posso corrigir isso e retornar a linha adequada de ambas as exibições?

Esta é a minha declaração VIEW

CREATE OR REPLACE VIEW inv_groups_vw AS 
 SELECT gg.id,
    gg.sku,
    gg.name,
    gg.description,
        CASE
            WHEN count(i.*) = 0 THEN '[]'::json
            ELSE json_agg(i.*)
        END AS items
   FROM ( SELECT g.id,
            g.sku,
            g.name,
            g.description,
            m.item_id
           FROM inv_items g
      LEFT JOIN inv_items_group_members m ON g.id = m.group_id
     WHERE g.is_group = true
     ORDER BY m.item_index) gg
   LEFT JOIN inv_items i ON gg.item_id = i.id AND i.is_group = false
  GROUP BY gg.id, gg.sku, gg.name, gg.description;

E realizando SELECT * FROM inv_groups_vwresultado

{
   "id": 3
   "sku": "PVC-1861-J",
   "name": "PVC 18 oz (jaune)",
   "description": "Rouleau de PVC 18 onces de couleur jaune",
   "items": [
      {
         "id":1,
         "sku":"PVC-186150-J",
         "name":"PVC 18 oz 61 in 50 vg (jaune)",
         "description":"...",
         "is_group":false
      }, {
         "id":2,
         "sku":"PVC-1861100-J",
         "name":"PVC 18 oz 61 in 100 vg (jaune)",
         "description":"...",
         "is_group":false
      }
   ]
}

No entanto, se eu executar o SELECT * FROM inv_groups_vw WHERE sku = 'PVC-1861-J';resultado

{
   "id": 3
   "sku": "PVC-1861-J",
   "name": "PVC 18 oz (jaune)",
   "description": "Rouleau de PVC 18 onces de couleur jaune",
   "items": [
      {
         "id":2,
         "sku":"PVC-1861100-J",
         "name":"PVC 18 oz 61 in 100 vg (jaune)",
         "description":"...",
         "is_group":false
      }, {
         "id":1,
         "sku":"PVC-186150-J",
         "name":"PVC 18 oz 61 in 50 vg (jaune)",
         "description":"...",
         "is_group":false
      }
   ]
}

Por que ORDER BY m.item_indexnão é respeitado?

Atualizar

Isso pode parecer funcionar, mas é mesmo?

CREATE OR REPLACE VIEW inv_groups_vw AS 
 SELECT gr.id,
    gr.sku,
    gr.name,
    gr.description,
    json_agg(gr.item) AS items
   FROM ( SELECT gg.id,
            gg.sku,
            gg.name,
            gg.description,
                CASE
                    WHEN i.id IS NULL THEN '{}'::json
                    ELSE row_to_json(i.*)
                END AS item
           FROM ( SELECT g.id,
                    g.sku,
                    g.name,
                    g.description,
                    m.item_id,
                    m.item_index
                   FROM inv_items g
              LEFT JOIN inv_items_group_members m ON g.id = m.group_id
             WHERE g.is_group
             ORDER BY m.item_index) gg
      LEFT JOIN inv_items i ON gg.item_id = i.id AND i.is_group = false
     ORDER BY gg.item_index) gr
  GROUP BY gr.id, gr.sku, gr.name, gr.description;

Eu tenho uma consulta como:

SELECT a.id, a.name, json_agg(b.*) as "item"
  FROM a
  JOIN b ON b.item_id = a.id
 GROUP BY a.id, a.name;

Como posso selecionar as colunas bpara não ter b.item_idno objeto JSON?

Eu li sobre ROW, mas ele retorna um objeto JSON como:

{"f1": "Foo", "f2": "Bar"}

Eu precisaria remapear o objeto JSON uma vez que ele fosse buscado para corresponder às chaves de coluna apropriadas. Eu gostaria de evitar isso e manter os nomes das colunas originais.

Dado um layout de tabela que se parece com isso

inv_items
=========
* id bigserial
sku character varying(22)
name character varying(32)
...

inv_items_stock
===============
item_id bigint (inv_items)
qty int
...

inv_items_group
===============
group_id bigint (inv_items)
item_id bigint (inv_items)

onde um item pode ser um "item de estoque" ou um "item de grupo" (com itens compostos...) tenho um gatilho que verifica antes de inserir/atualizar em cada um inv_items_stocke inv_items_grouppara garantir que algumas condições sejam atendidas; por exemplo, um item de estoque não pode ser um item de grupo.

O problema é que, em meu programa, executo um lote de inserções dentro de uma transação; primeiro eu insiro em inv_items, depois em uma das outras tabelas. Infelizmente, SELECTdentro da minha função trigger não encontra nenhum item que acabei de inserir dentro da transação, assim sempre falha.

Por exemplo, aqui está o gatilho de verificação de item de grupo

CREATE FUNCTION inv_check_set_group_items() RETURNS trigger AS $INV_GRP_SET$
DECLARE found_item bigint;
BEGIN
   SELECT id INTO found_item FROM inv_groups_vw WHERE id = NEW.group_id;
   IF found_item IS NULL THEN
       RAISE EXCEPTION 'Invalid group item';
   END IF;
   RETURN NEW;
END;
$INV_GRP_SET$ LANGUAGE plpgsql;

CREATE TRIGGER inv_check_set_group_items BEFORE INSERT OR UPDATE ON inv_items_group
FOR EACH ROW EXECUTE PROCEDURE inv_check_set_group_items();

Onde inv_groups_vwé um VIEWcom o predefinido SELECTpara retornar todos os grupos de itens dentro inv_itemsde .

O problema é o VIEWou o FUNCTION? O que posso fazer para corrigir isso e poder selecionar linhas de itens recém-inserido dentro da transação? ... estou perdendo mais alguma coisa?

Será que minha condição IF found_item IS NULL THENé falha?

Já fiz isso antes, mas antes de repetir sem pensar algo que considero um hack, estou perguntando aqui.

Eu tenho 3 tabelas (detalhes deixados de fora para maior clareza):

inv_items

id bigserial (PK)
sku character varying(24)
name character varying(32)
...

inv_item_groups

id bigserial (PK)
name cahracter varying(32)
...

inv_item_group_members

item_group_id bigint (FK -> inv_item_groups)
item_id bigint (FK -> inv_items)

Agora, no meu código, tenho um objeto assim (em pseudo-código)

class ItemGroup
   id:long
   groupName:String
   items:long[]

e esses objetos podem ser modificados, então precisam ser atualizados. Como quero preservar a integridade da chave, preciso da inv_item_group_memberstabela (caso contrário, teria usado outras soluções ).

Agora, a maneira usual que eu estava fazendo isso era

DELETE FROM inv_item_group_members WHERE item_group_id = $1
-- where $1 is the object's id

o, para cada itemsno objeto

INSERT INTO inv_item_group_members (item_group_id, item_id) VALUES ($1, $2)

Existe uma solução melhor? Quais são as alternativas? Estou pensando em uma função SQL, mas não tenho certeza de qual é a melhor abordagem aqui (ainda não tenho muita experiência com PGSQL). Li sobre gravável CTE , mas não aborda o caso em que os elementos são removidos de a matriz (ou seja, associação removida).