PostgreSQL 函数 TEMP TABLE 替代解决方案

本文关键字：解决方案 TABLE 函数 TEMP PostgreSQL | 更新日期: 2023-09-27 18:31:12

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 表，而是我需要一些我不必在 sesion 中删除的东西。

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

我尝试在 RETURN $1 之后和之前添加 DROP 表"临时";但它不起作用。

PostgreSQL 函数 TEMP TABLE 替代解决方案

我认为您不需要为简单的查询创建一个 TEMP 表（甚至不需要创建一个函数）。如果您使用的是提供 WITH 查询的 Postgresql 9.x，则更是如此，您应该阅读有关该内容的信息。

请检查文档的这一部分。目前还不清楚您希望实现什么，但我猜，您需要一个集合返回函数。

我希望这段代码可以满足您的需求：

CREATE OR REPLACE FUNCTION "getArticulos"(
    OUT "Articulo"."idArticulo",
    OUT "Articulo"."Nombre",
    OUT "Articulo"."Descripcion",
    OUT "Articulo"."idFamilia",
    OUT "Familia"."Nombre",
    OUT "Articulo"."idTipo",
    OUT "Tipo"."Nombre",
    OUT int4, -- I cannot get the source table for “Stock” column
    OUT "Articulo"."MinStock",
    OUT "Articulo"."MinStock"
    )
RETURNS SETOF record
AS $BODY$
BEGIN
    RETURN QUERY
    WITH "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")
        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";
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

由于显而易见的原因，我无法测试此功能。另外，请考虑将LANGUAGE更改为 SQL ，因为 plpgsql 开销没有理由执行此操作。

相关文章：