CODEWITHSUNDEEP
postgresqlparameter-passingplpgsqldynamic-sql
user2210516
user2210516

Reputation: 683

How to nest variable in query string passed to function

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)

enter image description here

Upvotes: 0

Views: 1625

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

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:

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:

Upvotes: 1

Related Questions