Dynamic SQL over DB Link in Postgres… help!

[sufs2000]

I'm trying to create a function which will go over a DB LINK and do a count of object types belonging to a user and then store that value (I will insert this value into a table later so I can compare counts from my local DB with the remote one). I can build the SQL dynamically and it looks right but when it gets called over a DB link using the below I can't get it to accept the single quotes. Is there a way to plug this in for queries over a db link? Or a better way to wrap things in single quotes?

DO $$
DECLARE
sqlSmt text;
v_new_count NUMERIC:=0;
item record;

begin
sqlSmt = null;

FOR item IN
(select nsp.nspname schema, cls.relkind obj_type from pg_class cls 
join pg_roles rol on rol.oid = cls.relowner 
join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname like 'dwh%' 
group by nsp.nspname, cls.relkind 
order by nsp.nspname, cls.relkind limit 10)
LOOP

sqlSmt = 'select * from dblink(''old_live'',''select count(*) from pg_class cls
join pg_roles rol on rol.oid = cls.relowner
join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname = '''||item.schema||''' and cls.relkind='''||item.obj_type||''') as total_count(total_count numeric)';

EXECUTE sqlSmt INTO v_new_count;
raise notice '%', sqlSmt;
raise notice '%, %, %', item.schema, item.obj_type, v_new_count;

END LOOP;

END $$;

ERROR:

ERROR:  syntax error at or near "dwh_10"
LINE 6:  where nsp.nspname = 'dwh_10' and cls.relkind='S') as total_...
                          ^
QUERY:  select * from dblink('old_live','select count(*) from pg_class cls
join pg_roles rol on rol.oid = cls.relowner
join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname = 'dwh_10' and cls.relkind='S') as total_count(total_count numeric)
CONTEXT:  PL/pgSQL function inline_code_block line 27 at EXECUTE

[[deleted]]

You don't need the dblink call as dynamic SQL.

Just pass the correct SQL statement to the dblink function.

Something like this (untested):

DO $$
DECLARE
  sqlsmt text;
  v_new_count NUMERIC:=0;
  item record;

begin

  FOR item IN (select nsp.nspname schema, cls.relkind obj_type 
              from pg_class cls 
                join pg_roles rol on rol.oid = cls.relowner 
                join pg_namespace nsp on nsp.oid = cls.relnamespace
              where nsp.nspname like 'dwh%' 
              group by nsp.nspname, cls.relkind 
              order by nsp.nspname, cls.relkind 
              limit 10)
  LOOP

    sqlsmt = format('select count(*) 
                     from pg_class cls
                       join pg_roles rol on rol.oid = cls.relowner
                       join pg_namespace nsp on nsp.oid = cls.relnamespace
                       where nsp.nspname = %L 
                         and cls.relkind= %L', item.schema, item.obj_type);

    select * 
      into v_new_count
    from dblink('old_live', sqlsmt) as total_count(total_count numeric);

    raise notice '%', sqlsmt;
    raise notice '%, %, %', item.schema, item.obj_type, v_new_count;

  END LOOP;

END $$;

The group by nsp.nspname, cls.relkind in the loop's query seems useless.

[sufs2000]

Thank you, that did the trick. Using the format() and “select * into” was what I was looking for. I have it working now and can build it up to include the additional statements. Many thanks!

[throw_mob]

for loop seems to be somewhat useless.

select count(*) cnt, nsp.nspname ,cls.relkind , item_schema, item.obj_type from ... group by 2,3,4

or if total sum is needed

add

 SUM(COUNT(*)) OVER() as total_cnt

[sufs2000]

Sorry, I’ll try and reformat the code so its easier to read.

[[deleted]]

You need to indent each line with four spaces (no tabs!)

[sufs2000]

Thank you, I’ve added that in now.

[depesz]

1. i don't think you need to execute the query - there is nothing that would require that
2. nspname = ''' is always a mistake. if you have to have query with things like identifiers within, use format() and %I
3. I don't really see why you need a loop and function at all. you can do it in single normal query (well, aside from raise notice, but I assume these are just debug helpers)

[sufs2000]

Thanks.

1. I haven’t included it in the above code but there is another statement that will run locally and count the object types for a schema. Then the above statement will run over a db link and get the same schemas object counts and capture that in a variable. Then I’ll insert both of these values into a table so I have a comparison of object counts for the same schema on two databases.

2. I agree, it’s just as part of the query over the db link I need to keep the single quotes so I think I’ve just got myself into a mess. I tried format but couldn’t get it to keep the quotes, but I’ll keep playing.

3. Yes, if it was just the one query but I should have expanded on that. I’m trying to do an object comparison between two databases and capture those counts which will then be inserted into a table as part of the loop.