Thread: cross-database references are not implemented

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hoi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have a database with 2 schemas:</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">- public</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">- export</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">In the export schema I have tables that are filled during an export process.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">There is some data I want to have in a table in the public schema as well.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">I wrote a trigger function that after insert in the export table does an export in the public
table.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">If I do an insert I get the error message: "ERROR:  cross-database references are not
implemented".</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">How can I solve this?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="NL"
style="font-size:10.0pt;
font-family:Arial">Met vriendelijke groet,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="NL"style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="NL"
style="font-size:12.0pt">PaulDam</span></font></div> 

Hello

it works for me

postgres=# create schema export;
CREATE SCHEMA
Time: 45,918 ms
postgres=# create table public.a(a varchar);
CREATE TABLE
Time: 91,385 ms
postgres=# create table export.a(a varchar);
\CREATE TABLE
Time: 9,462 ms
postgres=# create function ftrg() returns trigger as $$begin insert
into export.a values(new.*); return new; end$$ language plpgsql;
CREATE FUNCTION
Time: 486,395 ms
postgres=# \h CREATE trigger
Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }   ON table [ FOR [ EACH ] { ROW | STATEMENT } ]   EXECUTE
PROCEDUREfuncname ( arguments )
 

postgres=# CREATE TRIGGER aaa after insert on public.a for each row
execute procedure ftrg();
CREATE TRIGGER
Time: 5,848 ms
postgres=# insert into public.a values('ahoj');
INSERT 0 1
Time: 5,179 ms
postgres=# SELECT * from export.a ; a
------ahoj
(1 row)

postgresql 8.3

Pavel

2008/6/3 Paul Dam <p.dam@amyyon.nl>:
> Hoi,
>
>
>
> I have a database with 2 schemas:
>
> - public
>
> - export
>
>
>
> In the export schema I have tables that are filled during an export process.
>
> There is some data I want to have in a table in the public schema as well.
>
> I wrote a trigger function that after insert in the export table does an
> export in the public table.
>
>
>
> If I do an insert I get the error message: "ERROR:  cross-database
> references are not implemented".
>
>
>
> How can I solve this?
>
>
>
> Met vriendelijke groet,
>
>
>
> Paul Dam

On Tuesday 03 June 2008 6:12 am, Paul Dam wrote:
> Hoi,
>
>
>
> I have a database with 2 schemas:
>
> - public
>
> - export
>
>
>
> In the export schema I have tables that are filled during an export
> process.
>
> There is some data I want to have in a table in the public schema as
> well.
>
> I wrote a trigger function that after insert in the export table does an
> export in the public table.
>
>
>
> If I do an insert I get the error message: "ERROR:  cross-database
> references are not implemented".
>
>
>
> How can I solve this?
>
>
>
> Met vriendelijke groet,
>
>
>
> Paul Dam

You will need to show the actual query, but I suspect you have an extra period 
in your table name. Instead of public.table_name you have something like 
public.table.name.
-- 
Adrian Klaver
aklaver@comcast.net

Thanks Adrian,

I casted a column to a type with "value::<schema>.<table>.<column>%type"
That was the problem.

Met vriendelijke groet,
Paul Dam

-----Oorspronkelijk bericht-----
Van: Adrian Klaver [mailto:aklaver@comcast.net]
Verzonden: dinsdag 3 juni 2008 15:57
Aan: pgsql-sql@postgresql.org
CC: Paul Dam
Onderwerp: Re: [SQL] cross-database references are not implemented

On Tuesday 03 June 2008 6:12 am, Paul Dam wrote:
> Hoi,
>
>
>
> I have a database with 2 schemas:
>
> - public
>
> - export
>
>
>
> In the export schema I have tables that are filled during an export
> process.
>
> There is some data I want to have in a table in the public schema as
> well.
>
> I wrote a trigger function that after insert in the export table does
an
> export in the public table.
>
>
>
> If I do an insert I get the error message: "ERROR:  cross-database
> references are not implemented".
>
>
>
> How can I solve this?
>
>
>
> Met vriendelijke groet,
>
>
>
> Paul Dam

You will need to show the actual query, but I suspect you have an extra
period
in your table name. Instead of public.table_name you have something like

public.table.name.
--
Adrian Klaver
aklaver@comcast.net