Creating a function in postgresql dont make INSERT

Question

cursreg returns all rows on "continguts" that not have registers on classcont. (this is ok) But i need to insert on "classcont" the int recived on function (classificado int) for each result.

CREATE OR REPLACE FUNCTION fclassifica(clasificado int) RETURNS void AS $$
DECLARE
registre RECORD;
        cursreg CURSOR FOR SELECT c.idcontingut FROM continguts as c
WHERE NOT EXISTS (SELECT * FROM classcont as ca
WHERE c.idcontingut = ca.contingut);
BEGIN
OPEN cursreg;
LOOP
FETCH FROM cursreg INTO registre;
INSERT INTO classcont (class,contingut)
VALUES (clasificado, registre.idcontingut);
RAISE NOTICE 'La classificació de ID ha estat modificada' ;

END LOOP;
CLOSE cursreg;
RETURN ;
END;
$$ LANGUAGE plpgsql;

user330315 · Accepted Answer · 2021-04-15 18:58:17Z

1

No need for a cursor or even PL/pgSQL

CREATE OR REPLACE FUNCTION fclassifica(p_clasificado int) 
  RETURNS void 
AS
$$
  INSERT INTO classcont (class, contingut)
  select p_clasificado, c.idcontingut
  FROM continguts as c
  WHERE NOT EXISTS (SELECT * 
                    FROM classcont as ca
                    WHERE c.idcontingut = ca.contingut);
$$
language sql;

answered Apr 15, 2021 at 18:58

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a function in postgresql dont make INSERT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related