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
– wildplasser, Commented Mar 23, 2022 at 12:33
@wildplasser can you please show how i can implement that in my code and why should i use that? — user2210516
– user2210516, Commented Mar 23, 2022 at 13:31

Erwin Brandstetter · Accepted Answer · 2022-03-30 07:03:05Z

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, 2022 at 7:03

answered Mar 29, 2022 at 3:50

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2210516 Over a year ago

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);

Erwin Brandstetter Over a year ago

@user2210516 Yes, that was left over from before I broke out srv. Fixed now.

user2210516 Over a year ago

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);

Erwin Brandstetter Over a year ago

@user2210516 No $ here. You might be thinking of my other answer? stackoverflow.com/a/71656813/939860

Collectives™ on Stack Overflow

Doesn't find variable when passing query as parameter

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related