Início / dba / Perguntas / 141419
Accepted
jlandercy
Asked: 2016-06-17 05:02:17 +0800 CST

PostgreSQL: Passar tabela como argumento na função

  • 772

Estou descobrindo TYPEno PostgreSQL. Tenho um TABLE TYPEque alguma tabela deve respeitar (interface). Por exemplo:

CREATE TYPE dataset AS(
    ChannelId INTEGER
   ,GranulityIdIn INTEGER
   ,GranulityId INTEGER
   ,TimeValue TIMESTAMP
   ,FloatValue FLOAT
   ,Status BIGINT
   ,QualityCodeId INTEGER
   ,DataArray FLOAT[]
   ,DataCount BIGINT
   ,Performance FLOAT
   ,StepCount INTEGER
   ,TableRegClass regclass
   ,Tags TEXT[]
   ,WeightedMean FLOAT
   ,MeanData FLOAT
   ,StdData FLOAT
   ,MinData FLOAT
   ,MaxData FLOAT
   ,MedianData FLOAT
   ,Percentiles FLOAT[]
);

Eu posso criar uma tabela usando este modelo com:

CREATE TABLE test OF dataset;

Já vi muitas opções na API , mas estou um pouco perdido. Gostaria de saber se é possível atribuir esse tipo aos INPUT/OUTPUTparâmetros da função.

Digamos que eu tenha um FUNCTIONchamado processque receba uma amostra de registros de um dataset TABLE source, os processe e depois retorne um TABLE sinkcom o mesmo TYPE.

Ou seja, gostaria de saber se é possível criar um TYPEque se comporte assim:

CREATE FUNCTION process(
    input dataset
) RETURNS dataset
AS ...

E isso pode ser chamado assim:

SELECT
    *
FROM
    source, process(input := source) AS sink;

Eu me pergunto se isso é possível com o PostgreSQL e pergunto como fazer isso. Alguém de vocês sabe?

Aqui está um MWE do que estou tentando fazer:

DROP TABLE IF EXISTS source;
DROP FUNCTION IF EXISTS process(dataset);
DROP TYPE dataset;

CREATE TYPE dataset AS (
    id INTEGER
   ,t  TIMESTAMP
   ,x  FLOAT
);


CREATE TABLE source OF dataset;
ALTER TABLE source ADD PRIMARY KEY(Id);
INSERT INTO source VALUES
    (1, '2016-01-01 00:00:00', 10.0)
   ,(2, '2016-01-01 00:30:00', 11.0)
   ,(3, '2016-01-01 01:00:00', 12.0)
   ,(4, '2016-01-01 01:30:00',  9.0)
   ;

CREATE OR REPLACE FUNCTION process(
    _source dataset
)
RETURNS SETOF dataset
AS
$BODY$
SELECT * FROM source;
$BODY$
LANGUAGE SQL;

SELECT * FROM process(source);

Mas não tem sucesso, é como se a fonte fosse percebida como uma coluna em vez de uma SETOF RECORDScom o tipo de conjunto de dados.

postgresql functions

2 respostas

  1. Best Answer

    O parâmetro _sourceno MWE (exemplo de trabalho mínimo) não é referenciado em nenhum lugar. O identificador sourceno corpo da função não tem sublinhado à esquerda e é interpretado como nome de tabela constante de forma independente.

    Mas não funcionaria assim de qualquer maneira. SQL só permite parametrizar valores em instruções DML. Ver:

    Solução

    Você ainda pode fazê-lo funcionar usando SQL dinâmico com EXECUTEuma função plpgsql:

    CREATE TYPE dataset AS (id integer, t timestamp, x float);

CREATE TABLE source OF dataset (PRIMARY KEY(Id));  -- add constraints in same command

INSERT INTO source VALUES
    (1, '2016-01-01 00:00:00', 10.0)
   ,(2, '2016-01-01 00:30:00', 11.0);

CREATE OR REPLACE FUNCTION process(_tbl regclass)
  RETURNS SETOF dataset AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || _tbl;
END
$func$  LANGUAGE plpgsql;

SELECT * FROM process('source');  -- table name as string literal

    Ver:

    Ou procure perguntas e respostas relacionadas no site.

    Para fazê-lo funcionar para qualquer tabela:

    CREATE OR REPLACE FUNCTION process2(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || pg_typeof(_tbl);
END
$func$  LANGUAGE plpgsql;

SELECT * FROM process2(NULL::source);  -- note the call syntax!!

    Explicação detalhada:

    • 18

  2. Isso fará o que você deseja sem precisar de nenhum SQL dinâmico :

    drop table if exists source cascade;
drop function if exists process(dataset) cascade;
drop type if exists dataset cascade;

create type dataset as (
    id integer
   ,t  timestamp
   ,x  float
);

create table source of dataset;
alter table source add primary key(id);
insert into source values
   (1, '2016-01-01 00:00:00', 10.0)
 , (2, '2016-01-01 00:30:00', 11.0)
;

create or replace function process(
    x_source dataset[]
)
returns setof dataset
as
$body$
select * from unnest(x_source);
$body$
language sql;

select *
from
  process(
    array(
      select
        row(id, t, x)::dataset
      from source
    )
  );

    Tanto quanto eu posso dizer (depois de pesquisar extensivamente, porque eu tive o mesmo problema) você não pode passar uma tabela diretamente para uma função.

    No entanto, conforme mostrado, você pode transformar uma tabela em uma matriz []de um tipo personalizado que consiste em vários tipos básicos (semelhante a uma definição de tabela).

    Em seguida, você pode passar esse array e desaninha-lo de volta em uma tabela quando estiver na função.

    • 8

relate perguntas