I have encountered an issue in testing our upgrade from 9.4.4 to 9.6.1. Per the changes detailed in
https://www.postgresql.org/message-id/E1aGVwY-0002Pu-Uk%40gemulon.postgresql.org,the argument types of the to_reg*()
functionswere changed from cstring to text.
We have some plpgsql helper functions for our developers that run some dynamic SQL. At the time, we had followed the
solutionof doing a hard cast to cstring as found on Stack Overflow at
http://stackoverflow.com/questions/31648730/postgres-convert-text-to-string-or-difference-between-abc-vs-a-bcandhttp://stackoverflow.com/questions/33952892/use-dynamically-created-name-in-to-regclass-function,because there are no
implicitconversions between cstring to text nor text to cstring.
I am now faced with having to support both 9.4 and 9.6 as we will not be upgrading all servers simultaneously. The
solutionI have come up with is to use if-else statements that check the numeric version of the cluster the function is
installedon. An example:
if current_setting('server_version_num')::integer < 90600 then
raise notice '%', to_regclass((myschema||'. '||mytable)::cstring);
else
raise notice '%', to_regclass(myschema||'. '||mytable);
end if;
I wanted to confirm, is this the best way to approach this?
--
Michael Rasmussen
Sr. Data Engineer
Porch