How to nest variable in query string passed to function

Question

I need to be able to get the value stored inside rec_key.empname when I call this function:

CREATE OR REPLACE FUNCTION public.txt(text)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
declare
    var_param text;
    var_req TEXT;
    rec_key record;
    cur_key CURSOR FOR Select empname::varchar from employee;
BEGIN
open cur_key;
loop
   fetch cur_key into rec_key;
   EXIT WHEN NOT FOUND;
var_req := 
'
' || $1 || '
';
return query execute var_req;  
end loop;
close cur_key;
END 
$function$
;

What do I have to change to get the desired empname when calling the function?

If I call it like this it doesn't work: :(

select * from public.txt('select empid, age::integer,''''''|rec_key.empname|''''''::varchar from employee') as (empid integer, age integer, empname varchar)

Your function is defined as `public.txt`, but you call `public.grem_txt`. But first, tell us what you *really* need. You certainly don't need anything like you show here. Looks like you want to execute a passed `SELECT` query repeatedly with `employee.empname` added somehow? Start by declaring your version of Postgres, then (the relevant part or) the table definition for `employee`, and then describe your actual objective. — Erwin Brandstetter, Mar 26 '22 at 21:23
Hallo @erwinbrandstetter ! Actually i posted another queston before with the infos you ask for but i got no response so i rewrote the question to be more simpler. Here is this first one. Please have a look at this one and tell me if you need more information. I would really appreciate your help. https://stackoverflow.com/questions/71585308/doesnt-find-variable-when-passing-query-as-parameter — user2210516, Mar 28 '22 at 05:46

score 1 · Answer 1 · answered Mar 29 '22 at 05:18

To address the question asked:

CREATE OR REPLACE FUNCTION public.txt(_sql text)
  RETURNS SETOF record
  LANGUAGE plpgsql AS
$func$
DECLARE
   _rec record;
BEGIN
   FOR _rec IN 
      SELECT empname::text FROM employee
   LOOP
      RETURN QUERY EXECUTE _sql
      USING _rec.empname;
   END LOOP;
END
$func$;

Call:

SELECT * FROM public.txt('SELECT empid, age::integer, $1 AS empname FROM employee')
       AS (empid integer, age integer, empname varchar);

The example does not make any sense, though, and all of it could be replaced with a simple query. See my anser to your earlier question:

Doesn't find variable when passing query as parameter

Use the much simpler implicit cursor of a FOR loop. See:

Pass the variable as value with a USING clause. $1 is the symbol to reference the first USING argument. See:

Replace double quotes with single quotes in Postgres (plpgsql)

How to nest variable in query string passed to function

1 Answers1

Linked