Query with a dynamic custom field type field

user_11049921 0
Viewed 23

The custom field type parameter should be dynamic my code looks something like the following:

create or replace function somefunc(ip_address varchar, ip_type varchar) 
returns table(fielda varchar, 
    fieldb varchar,  
language plpgsql  
as $$   
begin   
   return query   
    execute format('select fielda, 
    fieldb, 
   
    from
   ips   
    join
   names on id = other_id
    where    
 iprange >>= ip_address::%s', ip_type); 
end;    
$$

As you can see I've tried using format, but then it thinks ip_address is a column.

plpgsql postgresql
1 Answers

The format should be %s (simple string) instead of %I (Identifier)

Here is a demo, concatenating the letter a with 1 and a custom cast:

create or replace function somefunc(ip_type varchar) 
returns setof text  
language plpgsql  
as $$   
begin   
   return query   
    execute format('select ''a''||1::%s', ip_type); 
end;    
$$; 

select somefunc('text');
 somefunc
----------
 a1
(1 row)

select somefunc('bool');
 somefunc
----------
 atrue
(1 row)

and in your original function:

create or replace function somefunc(ip_address varchar, ip_type varchar) 
returns table(fielda varchar, 
    fieldb varchar,  
language plpgsql  
as $$   
begin   
   return query   
    execute format('select fielda, 
    fieldb 
    from
   ips   
    join
   names on id = other_id
    where    
 iprange >>= ip_address::%s', ip_type); 
end;    
$$
user_76355690
Related