Erreur à l'exécution plgsql : ERROR: cannot open SELECT query as cursor

CREATE OR REPLACE FUNCTION pses(xcrit varchar, xidart integer) RETURNS TABLE(lib VARCHAR(100), ref varchar(50),stki1 numeric(13,2)) AS $$ BEGIN RETURN QUERY EXECUTE 'select stki1 into stki1 from f0011 where idart=' || xidart; <-- Source d'erreur RETURN QUERY EXECUTE 'SELECT design1,ref,stki1 FROM f0011 where design1 like ' || E'\'' || xcrit || E'\''; RETURN; END; $$ LANGUAGE plpgsql;

eydata02=# select * from pses('%ma%',36); ERROR: cannot open SELECT query as cursor CONTEXT: PL/pgSQL function pses(character varying,integer) line 3 at RETURN QUERY eydata02=#

Salut
La première RETURN QUERY doit avoir un SELECT ayant le même nombre de colonne (champs) que la table de retour. Et pas de INTO dans le SELECT.
Vous devez avoir quelques chose du genre...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION pses(xcrit varchar, xidart integer)
RETURNS TABLE(lib VARCHAR(100), ref varchar(50),stki1 numeric(13,2)) 
AS 
$$
BEGIN
   RETURN QUERY EXECUTE 'select x, y, z from f0011 where idart=' || xidart; 

   RETURN QUERY EXECUTE 'SELECT  design1,ref,stki1 
                                  FROM f0011 where design1 like ' || E'\'' || xcrit || E'\'';
   RETURN;
END;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION pses(xcrit varchar, xidart integer)
RETURNS TABLE(lib VARCHAR(100), ref varchar(50),stki1 numeric(13,2)) 
AS 
$$
BEGIN
   RETURN QUERY EXECUTE 'select x, y, z from f0011 where idart=' || xidart
   || ' UNION '
    || 'SELECT  design1,ref,stki1 FROM f0011 where design1 like ' || E'\'' || xcrit || E'\'';
   
RETURN;
END;

Par ailleurs, je voudrais comprendre like ' || E'\'' || xcrit || E'\''
@