Can someone tell me why i get this error and how i can solve it?

I have a function like this:

CREATE OR REPLACE FUNCTION custom.dblink_function_v21()
 RETURNS TABLE(name character varying, service character varying, scopes character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    var_req TEXT;

    rec_key record;
    cur_key CURSOR FOR Select * from dblink(
'host=server1
user=user1
password=pw1
dbname=db1
port=5433',
'
select s.id,s.name,s.host from servers s where s.id IN (465,325) order by s.name
') as (srv character varying,name character varying, host character varying);

BEGIN
open cur_key;
loop
   fetch cur_key into rec_key;
   EXIT WHEN NOT FOUND;
  var_req := 'Select * from dblink(
''host=' || rec_key.host || '
user=user2
password=pw2
dbname=db2'',
''
select
' || rec_key.name || ' as name ,
es.name as service,
es.scopes::varchar as scopes
from services es
'') as (srv varchar,service varchar,scopes varchar);
';
return query execute var_req; 

 end loop;
close cur_key;
END
$function$
;

when i call it with select * from custom.dblink_function_v21() i get this error :

SQL Error [42703]: ERROR: column "dba01" does not exist
  Where: Error occurred on dblink connection named "unnamed": could not execute query.
PL/pgSQL function custom.dblink_function_v21() line 43 at RETURN QUERY

dba01 is not a column.. it's my first value in column name.

If i just change the ' || rec_key.name || ' too ' || rec_key.srv || ' i suddenly works. But i don't want the id of the server in return i want the name. They both have same datatype and everything so i don't whats the Problem.

Ive been trying different things like crazy but nothing works..

Working with postgres v.10

CodePudding user response：

This is the select statement you create:

select XXX as name , es.name as service, es.scopes::varchar as scopes from services es

where XXX is the value of rec_key.name - and the value of rec_key.srv in the latter case.

In the former case, the statement evaluates to:

select dba01 as name , es.name as service, es.scopes::varchar as scopes from services es

but in the latter case:

select 100 as name , es.name as service, es.scopes::varchar as scopes from services es

In the former case, the error is issued as the column dba01 does not exist. In the latter case, the number 100 is selected as name. It is a perfectly valid statement as it is not interpreted as a column name.

If you want to select the text value "dba01" as column "name" you can change that part to:

''' || rec_key.name || '''::text as name ,

Best regards, Bjarni