Doesn't find variable when passing query as parameter

Question

I have a function with a static output that works. (Postgres v.10) This returns the Amount of users per Server.

Here is the code:

CREATE OR REPLACE FUNCTION public.test()
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
DECLARE
    var_req TEXT;
    var_error text;
    rec_key record;
    cur_key CURSOR FOR Select s.srv,s.host,s.port from public.connections() s where s.online = true;
BEGIN
open cur_key;
loop
   fetch cur_key into rec_key;
   EXIT WHEN NOT FOUND;

var_req := 
'Select * from dblink(
''host=' || rec_key.host || '
port=' || rec_key.port || '
user=**
password=**
dbname=mydb'',
''
select '''''|| rec_key.srv ||''''' as srv ,count (*) as total from users '') as (srv varchar,total integer)
';
return query execute var_req;  
end loop;
close cur_key;
END 
$function$
;

Output =

srv      total
rp1       50
sr2       41
xy        100

To be able to use reuse this query i want to move out the sql part so that i can pass it as parameter.

CREATE OR REPLACE FUNCTION public.test2(text)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
DECLARE
    var_req TEXT;
    var_error text;
    rec_key record;
    cur_key CURSOR FOR Select s.srv,s.host,s.port from public.connections() s where s.online = true;
BEGIN
open cur_key;
loop
   fetch cur_key into rec_key;
   EXIT WHEN NOT FOUND;

var_req := 
'Select * from dblink(
''host=' || rec_key.host || '
port=' || rec_key.port || '
user=**
password=**
dbname=**'',
''
' || $1 || '
';
return query execute var_req;  
end loop;
close cur_key;
END 
$function$
;

Now when i try to make exact the same query with the dynamic function i don't get it to work. Like this i am pretty close to my goal but instead of using what's inside the rec_key.srv variable it returns '''''|| rec_key.srv ||''''' :(

select * from public.test2('select ''''''''''''|| rec_key.srv ||'''''''''''' as srv ,count (*) as total from users '') as (srv varchar,total integer)') as (srv varchar,total integer)

Output =

            srv                   total
'''''|| rec_key.srv ||'''''        50
'''''|| rec_key.srv ||'''''        41
'''''|| rec_key.srv ||'''''        100

Can someone explain me how i can call what's inside the variable rec_key.srv with the new function?

Strong advice to use the `format()` function to build the dynamic query. — wildplasser, Mar 23 '22 at 12:33
@wildplasser can you please show how i can implement that in my code and why should i use that? — user2210516, Mar 23 '22 at 13:31

Erwin Brandstetter · Accepted Answer · 2022-03-30T07:03:05.973

1

Boils down to just this: (!)

SELECT s.srv, t.*
FROM   public.connections() s
     , dblink('host=' || s.host || ' port=' || s.port || ' user=** password=** dbname=**'
            , 'SELECT count(*) FROM users') AS t(total integer);

No wrapper function, no dynamic SQL, no cursor.
Just execute dblink() in an implicit CROSS JOIN LATERAL.

I also added srv to the result like you have in your first function. No need to pipe that through dblink.

See:

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Passing arbitrary queries is open to SQL injection. Be sure to understand possible implications and only execute it with trusted input. See:

edited Mar 30 '22 at 07:03

answered Mar 29 '22 at 03:50

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you so much for taking your time and help me! Had to take away srv text, to make it work though. Now it works like a charm. SELECT s.srv, t.* FROM public.connections() s , dblink('host=' || s.host || ' port=' || s.port || ' user=** password=** dbname=**' , 'SELECT count(*) FROM users') AS t(total integer); – user2210516 Mar 30 '22 at 06:41
@user2210516 Yes, that was left over from before I broke out `srv`. Fixed now. – Erwin Brandstetter Mar 30 '22 at 07:05
I don't really understand that how i know how i can change the s.host, and s.port with thoose $ symbol. would this work? SELECT s.srv, t.* FROM public.connections() s , dblink('host=' || 1$ || ' port=' || 2$ || ' user=** password=** dbname=**' , 'SELECT count(*) FROM users') AS t(total integer); – user2210516 Mar 30 '22 at 09:19
@user2210516 No `$` here. You might be thinking of my other answer? https://stackoverflow.com/a/71656813/939860 – Erwin Brandstetter Mar 30 '22 at 12:14

Doesn't find variable when passing query as parameter

1 Answers1

Linked