c# - PostgreSQL Function TEMP TABLE替代方案

Question

CREATE OR REPLACE FUNCTION "getArticulos"(refcursor)
RETURNS refcursor AS
$BODY$
BEGIN
    CREATE TEMP TABLE "Temporal" AS
    SELECT a."idArticulo", SUM("Stock") AS "Stock"
    FROM "ArticuloMarca" AS am, "Articulo" AS a
    WHERE a."idArticulo" = am."idArticulo"
    GROUP BY a."idArticulo"
    ORDER BY a."idArticulo";
    OPEN $1 FOR
        SELECT DISTINCT(a."idArticulo"), a."Nombre", a."Descripcion", a."idFamilia", f."Nombre" AS "Familia",a."idTipo", t."Nombre" AS "Tipo", tmp."Stock", a."MinStock", a."MaxStock"
        FROM "Articulo" AS a, "ArticuloMarca" AS am, "Familia" AS f, "Tipo" AS t, "Temporal" AS tmp
        WHERE a."idFamilia" = f."idFamilia" AND a."idTipo" = t."idTipo" AND a."idArticulo" = tmp."idArticulo"
        ORDER BY a."idArticulo";
    RETURN $1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

那是我的功能，但我一直试图不使用 TEMP TABLE，因为我需要一些我不必在 session 中删除的东西。

我试过使用 RECORD，一种叫做 refcursor 的东西，但我只得到一行，我需要整个结果。知道我可以使用什么吗？¿

我尝试在 RETURN $1 之后和之前添加 DROP TABLE "Temporal"；但它不起作用。

Answer 1

我认为您不需要为简单查询创建 TEMP 表(甚至也不需要创建函数)。如果您使用的是 Postgresql 9.x，则更是如此，它提供 WITH queries ，你应该读一读。