Início / dba / Perguntas / 135378
Accepted
tinlyx
Asked: 2016-04-15 19:46:15 +0800 CST

Como usar RETURNS TABLE com uma tabela existente no PostgreSQL?

  • 772

Da documentação do PostgreSQL aqui :

Existe outra maneira de declarar uma função como retornando um conjunto, que é usar a sintaxe RETURNS TABLE(columns). ... Essa notação é especificada em versões recentes do padrão SQL e, portanto, pode ser mais portátil do que usar SETOF.

Isso soa como se RETURNS TABLEfosse um estilo mais novo e portátil para retornar várias linhas. Mas não tenho certeza se as duas sintaxes são equivalentes.

Eu queria saber se podemos realmente usar RETURNS TABLEpara substituir RETURNS SETOF ?

Em particular, um caso que não descobri é: se tivermos uma tabela existente fooe seu tipo composto associado, como podemos usá-la em RETURNS TABLE?

Usando o exemplo do link acima, o seguinte pode ser reescrito usando RETURNS TABLE:

CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

Até agora, tentei usar RETURNS TABLE (foo.*)e RETURNS TABLE (foo), o que não funcionou.

postgresql postgresql-9.5

2 respostas

  1. Best Answer

    PS: desculpe meus comentários, estava desenvolvendo um código complexo e estou com um pequeno erro que parece uma restrição estúpida do PostgreSQL em uma parte de "tabela de retorno"... Fui estúpido, ao invés de me concentrar e resolver, usei a internet (mecanismo de busca me colocou aqui). Agora, esta wiki-resposta é para ajudar outros leitores, chamados pelo buscador e atraídos pelo título da pergunta.

    Obrigado a @dezso (foi uma resposta correta) e, por favor, todos os leitores, você pode editar esta pergunta para ser mais didática, é um Wiki.

    Desde o PostgreSQL-v8 podemos fazer isso! Podemos RETURNS EXISTING_TABLE_NAME

    Em seu Guia, em todas as versões do PostgreSQL, desde pg v8 até a versão atual , todas possuem uma seção chamada "Funções SQL como Fontes de Tabelas" . Vamos reproduzir o exemplo do Guia com algumas simplificações:

    CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1);

    Está funcionando como esperado, está perfeito!

     fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed

    A pergunta "Como usar RETURNS TABLE com uma tabela existente no PostgreSQL?" tenho uma boa resposta desde pg v8... É assim que fazemos nos últimos 15 anos, a sintaxe é:
      RETURNS SETOF <EXISTING_TABLE_NAME>     .

    Use a cláusula TABLE como CREATE TABLE instantâneo para retornar

    A confusão do @tinlyx, explicada na pergunta dele, é sobre o uso da cláusula ao TABLEinvés de SETOF... Para pensarmos usando a "lógica da sintaxe do PostgreSQL", devemos primeiro lembrar que ela RETURN <EXISTING_TABLE_NAME>também é válida, e tem o mesmo comportamento que RETURN <EXISTING_TYPE_NAME>. É natural retornar apenas uma linha.

    Próximo passo, lembre-se que declaramos uma tupla com a cláusula CREATE TABLE (<tuple_description>), portanto, uma boa sintaxe para expressar uma "tabela de definição instantânea de tupla" é RETURN TABLE (<tuple_description>), e faz sentido return TABLE -type, que é como um tipo de array, retornará várias instâncias (TABLE é um conjunto de tuplas).

    Próximo passo, lembre-se que declaramos uma tupla com a CREATE TABLE (<tuple_description>)cláusula, portanto, uma boa sintaxe para expressar uma "definição de tabela instantânea" é RETURN TABLE (<tuple_description>); e faz sentido retornar o tipo Table, que é como o tipo Array, eles retornam várias instâncias (TABLE é um conjunto de tuplas).

    A "coisa moderna" no PostgreSQL (!) é o que @ZiggyCrueltyfreeZeitgeister mostrou, a RETURNS TABLE (LIKE <table_name>)sintaxe.

    CREATE FUNCTION getfoo2(int) RETURNS TABLE (LIKE foo) AS $$ -- working fine!
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo2(1); -- same result as getfoo(1)

    Muitas maneiras de fazer o mesmo, um resumo:

    Explicando o nome da tabela (duas maneiras) ou o nome do tipo:

    • RETURNS TABLE (LIKE <table_name>)(moderno e bom)
    • RETURNS SETOF <table_name>(velho mas bom)
    • RETURNS SETOF <type_name>(depois CREATE TYPE <type_name> (<tuple_description>))

    Formas implícitas/genéricas, por tipos anônimos:

    • RETURNS SETOF RECORD(genérico, mas às vezes um problema)
    • (existir algo como?)RETURNS SETOF ROW?

    Definição de tabela instantânea:

    • RETURNS TABLE (<tuple_description>)
    • (no RETURNS) usando OUTna lista de parâmetros.

    Para o último caso, usando nosso exemplo para ilustrar:
    CREATE FUNCTION getfoo(int, OUT fooid int, OUT foosubid int, OUT fooname text)

    Para entradas dinâmicas e/ou polimóficas, você deve verificar esta explicação .

    Melhor prática?

    Existem muitas maneiras de fazer o mesmo, então, existe uma "melhor"?

    Como sintaxe prefiro o uso de RETURNS TABLE (LIKE <table_name>), que é explícito: sem confusão com "REGISTRO implícito", sem medo de incompatibilidades...

    Importante para gerenciamento de bibliotecas, DROP TABLE foo CASCADEvai largar também a função: em qualquer sintaxe ( returns tableou returns setof) o PostgreSQL fará um bom trabalho.

    drop table foo cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function getfoo(integer)
drop cascades to function getfoo2(integer)
    • 5

  2. Os diferentes modos são intercambiáveis ​​em algumas circunstâncias, mas são diferentes. O que RETURNS TABLEé bom está descrito na frase que você removeu de sua citação acima:

    Isso é equivalente a usar um ou mais parâmetros OUT mais marcar a função como retornando o registro SETOF (ou SETOF um único tipo de parâmetro de saída, conforme apropriado).

    Isso significa que CREATE FUNCTION ... RETURNS TABLEse destina a especificar um tipo de retorno personalizado, ou seja, é equivalente a CREATE TYPE xxx_t AS ([fields...,]); CREATE FUNCTION xxx ... RETURNS SETOF xxx_t, e também CREATE FUNCTION xxx ([in params...,] out [fields...,]) RETURNS SETOF RECORD.

    Ou seja, uma função não pode retornar uma tabela do tipo de outra tabela: use RETURNS SETOFdiretamente para isso (como você fez no trecho da sua pergunta).

    • 3

relate perguntas