PostgreSQL中的函数从一个表插入到另一个表? [英] Function in PostgreSQL to insert from one table to another?

问题描述

我有以下:

我拿到了桌子:

Equipos(团队)

Equipos (Teams)

Partidos(比赛)

Partidos (Matches)

列 num_eqpo_loc &num_eqpo_vis 从表 partidos 引用到表equipo.他们引用了 num_eqpo 列.正如你在这里看到的:

create table equipos
(num_eqpo serial,     
ciudad varchar (30),
num_gpo int, 
nom_equipo varchar (30), 
primary key (num_eqpo), 
foreign key (num_gpo) references grupos (num_gpo))

create table partidos 
(semana int, 
num_eqpo_loc int, 
num_eqpo_vis int, 
goles_loc int, 
goles_vis int, primary key (semana,num_eqpo_loc,num_eqpo_vis), 
foreign key (num_eqpo_loc) references equipos (num_eqpo), 
foreign key (num_eqpo_vis) references equipos (num_eqpo))

我想得到以下输出:

一方面，我创建了一个名为 general 的表:

In one hand, I created a table called general:

CREATE TABLE general
(
  equipo character varying(30) NOT NULL,
  partidos_jug integer,
  partidos_gana integer,
  partidos_emp integer,
  partidos_perd integer,
  puntos integer,
  goles_favor integer,
  CONSTRAINT general_pkey PRIMARY KEY (equipo)
)

另一方面，我有这个功能:

In the other, I have the function:

CREATE OR REPLACE FUNCTION sp_tablageneral ()  RETURNS TABLE (
    equipo character varying(30)
  , partidos_jug int
  , partidos_gana int
  , partidos_emp int
  , partidos_perd int
  , puntos int
  , goles_favor int) AS
$BODY$
DECLARE cont int:= (SELECT count(num_eqpo)FROM equipos);
r partidos%ROWTYPE;
BEGIN

    while cont>0
    LOOP

    SELECT INTO equipo nom_equipo FROM equipos AS E WHERE E.num_eqpo=cont;
    SELECT INTO partidos_jug COUNT(*) FROM partidos as P WHERE (P.num_eqpo_loc=cont OR P.num_eqpo_vis=cont);
    SELECT INTO partidos_gana COUNT(*) FROM partidos AS P WHERE (P.num_eqpo_loc=cont AND P.goles_loc>P.goles_vis OR P.num_eqpo_vis=cont AND P.goles_vis>P.goles_loc);
    SELECT INTO partidos_emp COUNT(*) FROM partidos AS P WHERE (P.num_eqpo_loc=cont AND P.goles_loc=P.goles_vis OR P.num_eqpo_vis=cont AND P.goles_loc=P.goles_vis);
    SELECT INTO partidos_perd COUNT(*) FROM partidos as P WHERE ( (P.num_eqpo_loc=cont AND P.goles_loc<P.goles_vis) OR (P.num_eqpo_vis=cont AND P.goles_loc>P.goles_vis));
    SELECT INTO puntos partidos_emp*1 + partidos_gana*3;
    SELECT INTO goles_favor SUM(goles_loc) FROM partidos as P WHERE P.num_eqpo_loc=cont + (SELECT SUM(goles_vis) FROM partidos as P WHERE P.num_eqpo_vis=cont);

    cont:= cont - 1;
    END LOOP;

  RETURN NEXT ; 
 END;
$BODY$ LANGUAGE plpgsql STABLE;

我希望函数显示我想要的输出 &我还希望表常规"具有与所需输出相同的值.

I want the function to show my desired Output & I also want the table 'General' to have the same values from the desired output.

有了这个功能，我就得到了:

With this function I just get:

我不知道如何看到想要的内容，因为我只是得到第一行数据.我也想知道如何从函数返回的表中插入到名为 General 的现有表中.

我也尝试过:

CREATE OR REPLACE FUNCTION sp_tablageneral ()  RETURNS TABLE (
    equipo character varying(30)
  , partidos_jug int
  , partidos_gana int
  , partidos_emp int
  , partidos_perd int
  , puntos int
  , goles_favor int) AS
$BODY$
DECLARE cont int:= (SELECT count(num_eqpo)FROM equipos);
r partidos%ROWTYPE;
BEGIN

    while cont>0
    LOOP

        SELECT INTO equipo nom_equipo FROM equipos AS E WHERE E.num_eqpo=cont;
    SELECT INTO partidos_jug COUNT(*) FROM partidos as P WHERE (P.num_eqpo_loc=cont OR P.num_eqpo_vis=cont);
    SELECT INTO partidos_gana COUNT(*) FROM partidos AS P WHERE (P.num_eqpo_loc=cont AND P.goles_loc>P.goles_vis OR P.num_eqpo_vis=cont AND P.goles_vis>P.goles_loc);
    SELECT INTO partidos_emp COUNT(*) FROM partidos AS P WHERE (P.num_eqpo_loc=cont AND P.goles_loc=P.goles_vis OR P.num_eqpo_vis=cont AND P.goles_loc=P.goles_vis);
    SELECT INTO partidos_perd COUNT(*) FROM partidos as P WHERE ( (P.num_eqpo_loc=cont AND P.goles_loc<P.goles_vis) OR (P.num_eqpo_vis=cont AND P.goles_loc>P.goles_vis));
    SELECT INTO puntos partidos_emp*1 + partidos_gana*3;
    SELECT INTO goles_favor SUM(goles_loc) FROM partidos as P WHERE P.num_eqpo_loc=cont + (SELECT SUM(goles_vis) FROM partidos as P WHERE P.num_eqpo_vis=cont);

    SELECT equipo, partidos_jug , partidos_gana, partidos_emp , partidos_perd , puntos , goles_favor INTO equipo,partidos_jug,partidos_gana,partidos_emp,partidos_perd,puntos,goles_favor FROM general;

    cont:= cont - 1;
        END LOOP;

  RETURN NEXT ; 
 END;
$BODY$ LANGUAGE plpgsql STABLE;

但我明白了:

ERROR: the reference to the column "equipo" is ambiguous 
LINE 1: SELECT equipo , partidos_jug, partidos_gana, partidos_emp ...
                ^
********** Error **********
ERROR: the reference to the column "equipo" is ambiguous 
SQL state: 42702
Detail: It could refer either to a variable PL / pgSQL as a column in a table.
Context: PL / pgSQL sp_tablageneral () function on line 17 in SQL statement

任何帮助都会很棒.

提前致谢！

推荐答案

你可以用纯 SQL 解决这个问题，你不需要一个函数.

You can solve this issue in pure SQL, you don't need a function for this.

最好的办法是将统计数据的收集分成两个不同的查询，一个是球队在主场比赛的时候，一个是他们在客场比赛的时候.为每场比赛计算得分和进球数.然后 UNION 这两个查询并将其用作子查询来计算整体统计信息:

The best thing is to break the collection of statistics into two distinct queries, one for when the team plays at home, one when they play away. For each game calculate the points and the goals scored. Then UNION those two queries and use that as a sub-query to calculate the overall stats:

SELECT
  eq.nom_equipo AS equipo,
  COUNT(p.*) AS partidos_jug,
  SUM(CASE WHEN p.puntos = 3 THEN 1 ELSE 0 END) partidos_gana,
  SUM(CASE WHEN p.puntos = 1 THEN 1 ELSE 0 END) partidos_emp,
  SUM(CASE WHEN p.puntos = 0 THEN 1 ELSE 0 END) partidos_perd,
  SUM(p.puntos) AS puntos,
  SUM(p.goles) AS goles_favor
FROM equipos eq
JOIN (
  -- Playing at home
  SELECT
    num_eqpo_loc AS eqpo, 
    CASE WHEN (goles_loc > goles_vis) THEN 3
         WHEN (goles_loc = goles_vis) THEN 1
         ELSE 0
    END AS puntos,
    goles_loc AS goles
  FROM partidos
  UNION
  -- Playing away
  SELECT
    num_eqpo_vis AS eqpo, 
    CASE WHEN (goles_vis > goles_loc) THEN 3
         WHEN (goles_vis = goles_loc) THEN 1
         ELSE 0
    END AS puntos,
    goles_vis AS goles
  FROM partidos) AS p ON p.eqpo = eq.num_eqpo
GROUP BY equipo
ORDER BY puntos DESC, partidos_jug ASC, goles_favor DESC;

由于 CASE 语句，这并不是特别快，但它会比使用过程和循环更快.

This is not particularly fast due to the CASE statements, but it will be faster than using a procedure and a loop.

我建议您使用上述查询CREATE VIEW general AS ...，而不是将此查询的结果放入表中.在这种情况下，当您 SELECT * FROM general 时，您总是会得到最新的结果，并且您不必在运行查询之前 TRUNCATE 通用表(使用数据添加新结果表中将违反 PK 约束).如果您确实需要该表，请在上面的查询中使用 SELECT ... INTO general FROM ....

Instead of putting the result of this query into a table, I would suggest that you CREATE VIEW general AS ... with the above query. In that case you always get the latest results when you SELECT * FROM general and you don't have to TRUNCATE the general table before running the query (adding new results with data in the table will violate the PK constraint). If you really need the table then use SELECT ... INTO general FROM ... in the query above.

这篇关于PostgreSQL中的函数从一个表插入到另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！