Call an Array result from select function to do update function with postgresql-CodePudding

I'm new with SQL functions and postgreSQL. I just try to select some mails of my compte table to update them afterwards so I create select and update functions but I have always the error:

"ERROR: query "SELECT emailAnonymisation()" returned more than one row CONTEXT: PL/pgSQL function updateMail() line 5 during statement block local variable initialization"

The problem is in my update function but I don't know if it's only a variable problem or a function logical problem...

My select function


CREATE OR REPLACE FUNCTION emailAnonymisation() 
RETURNS table (mail varchar)
LANGUAGE plpgsql 
AS $$
BEGIN
    return query
SELECT compte.mail
  FROM compte
  limit 100;
END 
$$;

My update function where I call the emailAnonymisation() function and where the problem is I think

CREATE OR REPLACE FUNCTION updateMail()
  RETURNS varchar[] AS
$BODY$
DECLARE
    _tbl varchar[]:=emailAnonymisation();
    t    text;
BEGIN

FOREACH t IN ARRAY _tbl
LOOP
    EXECUTE '
    UPDATE ' || t || '
    SET t = REPLACE(SUBSTR(t,LOCATE('@',t)   1),"X")
    WHERE LOCATE('@',t) > 0;';
END LOOP;

END;
$BODY$ LANGUAGE plpgsql;

the update call

select updateMail();

CodePudding user response：

Try using SETOF:

CREATE OR REPLACE FUNCTION emailAnonymisation() 
RETURNS SETOF string(mail varchar)
LANGUAGE plpgsql 
AS $$
BEGIN
    return query
SELECT compte.mail
  FROM compte
  limit 100;
END 
$$;

CodePudding user response：

Ok I have finally found what was the problem with the select I should have a return like this with RETURNS character varying[]

CREATE OR REPLACE FUNCTION emailAnonymisation() 
RETURNS character varying[]
AS $$
DECLARE 
    result character varying[];
BEGIN
SELECT ARRAY( SELECT compte.mail as mail
  FROM compte
  WHERE mail IS NOT NULL         
  limit 100)
  into result;
  return result;
END;
$$
LANGUAGE plpgsql;

And for the update the same type as the select function

CREATE OR REPLACE FUNCTION updateMail()
  RETURNS character varying(150) AS
$BODY$
DECLARE
    _tbl character varying[]:=emailAnonymisation();
    mail    text;
    endUrl text := '@mail.com';
    BeginningUrl text := random_string(15);
BEGIN
FOREACH mail IN ARRAY _tbl
LOOP
    UPDATE ' || t || '
    SET t = REPLACE(SUBSTR(t,LOCATE('@',t)   1),"X")
    WHERE LOCATE('@',t) > 0;';
END LOOP;