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

问题描述

我有以下内容:

我得到了桌子:

马术装备(团队)

Partidos(比赛)

Partidos (Matches)

列num_eqpo_loc&从表partidos引用到表equipo的num_eqpo_vis.他们引用了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.

使用此功能，我得到:

我不知道如何查看所需的内容，因为我仅获得第一行数据. 我还想知道如何将功能返回的表插入到名为General的现有表中.

I don't know how to see the desired content as I just get the first row of data. I also wonder how to Insert from the table returned by the fuction to the existing table called 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屋！