Início / dba / Perguntas / 317489
Accepted
Biller Builder
Asked: 2022-09-28 09:00:57 +0800 CST

Como você executa "SELECT" no argumento de tipo composto de uma função SQL?

  • 772

Exemplo: https://dbfiddle.uk/bCSwVpd9

Base de dados:

CREATE TABLE entities (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TYPE entity_id AS (
  id bigint
);

Função:

CREATE FUNCTION get_entities (
  pagination_limit bigint DEFAULT 25,
  pagination_offset bigint DEFAULT 0,
  entity_ids entity_id DEFAULT NULL
)
RETURNS TABLE (
  id bigint
)
LANGUAGE SQL
AS $BODY$
  WITH input_entities AS (
    SELECT
      id
    FROM
      entities
    WHERE
      -- filter by id list if provided
      entity_ids IS NULL OR id IN (
        SELECT
          id
        FROM
          entity_ids
      )
    ORDER BY
      id ASC
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id
  FROM
    input_entities
  ORDER BY
    id
$BODY$;

A muleta é que eu quero escrever uma função de seleção múltipla paginada que possa funcionar tanto a partir de informações de paginação quanto de um conjunto de ids. O problema com a função acima falha com:

ERROR:  relation "entity_ids" does not exist
LINE 22:           entity_ids

Existem respostas semelhantes para este problema: first , second . No entanto, eles giram em torno do argumento ser uma string identificadora, não um tipo de registro composto e também usar plpgsql, que pode ou pode não ser importante.

postgresql functions

1 respostas

  1. Best Answer

    O título da pergunta e o corpo da pergunta fazem perguntas diferentes, então vou abordar ambos.

    No que diz respeito ao título da pergunta: Você não precisa SELECTcontra um tipo composto, você pode referenciar campos nele diretamente usando a notação de ponto. Por exemplo, se seu tipo for definido como

    CREATE TYPE entity_id AS (
  id bigint,
  name text
);

    e uma instância do tipo é passada para uma função com um argumento chamado foo, você pode referenciar os campos como foo.ide foo.text, respectivamente.

    No que diz respeito ao corpo da pergunta: Você não pode passar um conjunto de registros como um argumento de função, mas pode convertê-lo em um array e passar isso. No seu caso, use um array como idargumento de lista e o = ANYoperador para pesquisá-lo:

    CREATE OR REPLACE FUNCTION get_entities(pagination_limit bigint DEFAULT 25, pagination_offset bigint DEFAULT 0, entity_ids bigint[] DEFAULT NULL)
 RETURNS TABLE(id bigint)
 LANGUAGE sql
AS $function$
  WITH input_entities AS (
    SELECT
      id
    FROM
      entities
    WHERE
      entity_ids IS NULL OR id = ANY ( entity_ids )
    ORDER BY
      id ASC
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id
  FROM
    input_entities
  ORDER BY
    id
$function$

    Ligue como:

    SELECT * FROM get_entities(25,0,ARRAY[1,2,3,4,5]::bigint[]);

    Ou para usar a saída de uma subconsulta, use o ARRAY()construtor:

    SELECT * FROM get_entities(25,0,ARRAY(SELECT ids FROM mytable ...));

    Como você queria ver como passar um conjunto de registros de uma função com valor de tabela para outra:

    SELECT * FROM get_entities(25,0,ARRAY(SELECT id FROM myfunc());

    Claro, o construtor é desnecessário se myfunc()retornar um array.

    As duas ideias expressas acima podem ser combinadas; você pode ter uma matriz de instâncias de tipo composto.

    Referência: https://www.postgresql.org/docs/14/arrays.html

    • 1

relate perguntas