Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Are there other location tables in other schemas? –  dezso Nov 20 '12 at 9:46
    
No, in this situation the table only exists once. –  Manuel Faux Nov 20 '12 at 10:10
    
After more testing, I could not make %TYPE work in assignment in any way. –  dezso Nov 20 '12 at 13:32

1 Answer 1

up vote 1 down vote accepted

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).

share|improve this answer
    
My problem is in the assign statement. I need to cast the '127.0.0.1' to the type of the alias column. –  Manuel Faux Nov 20 '12 at 10:32
    
@ManuelFaux 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. –  dezso Nov 20 '12 at 10:35
    
In this case the type is inet, so a implicit cast from '127.0.0.1' to inet is not possible. I tried to do an explicit cast with the code I've posted in my question, which worked as long as the table "scan" did not exist. –  Manuel Faux Nov 20 '12 at 11:46
    
I run a quick test on 9.1, it worked. Since normal inet input is just a single-quoted string, it should work. Have you tried it? –  dezso Nov 20 '12 at 11:50
1  
@ManuelFaux As I've already noticed above, I couldn't make it work. Without schema name it throws an other error, with it nor cross-reference. Furthermore, while it isn't mentioned explicitely int the docs, it is clear that %TYPE serves as a tool for declarations, not assignments. Compiling plpgsql functions does not reveal all errors (some are thrown only when running them), and it is quite possible that check_function_bodies was set to off at compile time. –  dezso Nov 22 '12 at 12:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.