Distinguish between schema and database

Question

I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code:

DECLARE
    v_tmp_host scan.location.alias%TYPE;
    v_ip_address character varying;
BEGIN
    -- Some assignment to v_ip_address

    v_tmp_host := v_ip_address::scan.location.alias%TYPE;

Since the code was working for a long time, I investigated and found out, that "scan" is a schema in the database this trigger exists in and someone created a database called "scan" some days ago. Since now "scan" is a db as well as a schema, pg seems to use the db instead of the schema.

My question is, how can I avoid such problems in future? How should I write this code to don't get broken by creating additional relations, databases or schemas?

Or in other words: How can I force pg to use the schema and not the db in this situation?

Answer 1

You have to declare your variable as the desired type. The %TYPE construct is for copying types, therefore it is usable in the DECLARE section only. In return, the assignment will be simpler:

DECLARE
    v_tmp_host scan.location.alias%TYPE;
    v_ip_address character varying;
BEGIN
    v_ip_address := '127.0.0.1';
    v_tmp_host := v_ip_address;
    [...]

The only change to your code is that I completely omitted the cast from the assignment. In this case, PostgreSQL won't try to resolve scan as a database name, since cross-database references are not implemented...

If you declared your variable with a certain type, assignments will try to cast the given value to that type. In case of failure you'll get an error. Since normal inet input is just a single-quoted string, a varchar will be accepted (if its format fits the inet input format).