Postgres ошибка ссылки между базами не реализованы

очень простое обновление базы данных postgresql, и оно не работает. Оператор sql select в порядке и возвращает правильные значения.

Когда я добираюсь до обновления, он выдает ошибку:

 {"ERROR [0A000] ERROR: cross-database references are not implemented: "openerp.public.product_template"; Error while executing the query"}.

Я использую vb.net и postgresql 9.2.

Все, что я хочу, это изменить поле имени, чтобы оно соответствовало описанию.

log:
LOG 0   duration: 34.000 ms  statement: SELECT * FROM product_template where import_date = '08/22/2013'
LOG 0   duration: 11.000 ms  statement: select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 20496) inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum
LOG 0   duration: 12.000 ms  parse _PLAN000000001D2CFB60: SELECT * FROM product_template where import_date = '08/22/2013'
LOG 0   duration: 11.000 ms  statement: select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class tc, pg_catalog.pg_index i, pg_catalog.pg_namespace n, pg_catalog.pg_class ic where tc.oid = 20496 AND tc.oid = i.indrelid AND n.oid = tc.relnamespace AND i.indisprimary = 't' AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped) AND ic.oid = i.indexrelid order by ia.attnum
LOG 0   duration: 0.000 ms  statement: select current_schema()
LOG 0   duration: 1.000 ms  statement: select c.relhasrules, c.relkind, c.relhasoids from pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid = c.relnamespace and c.relname = 'product_template' and u.nspname = 'public'
LOG 0   duration: 1.000 ms  statement: select c.relhasrules, c.relkind, c.relhasoids from pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid = c.relnamespace and c.relname = 'product_template' and u.nspname = 'public'
ERROR   0A000   cross-database references are not implemented: "openerp.public.product_template"

Код:

Private Sub btnChgNameToDescr_Click(sender As Object, e As EventArgs) Handles btnChgNameToDescr.Click

    Dim objConn As New System.Data.Odbc.OdbcConnection
    Dim objCmd As New System.Data.Odbc.OdbcCommand
    Dim dtAdapter As New System.Data.Odbc.OdbcDataAdapter
    Dim ds As New DataSet

    Me.Cursor = System.Windows.Forms.Cursors.WaitCursor

    Dim strConnString As String
    Dim strSQL As String
    Dim iRecCount As Integer

    Me.Cursor = System.Windows.Forms.Cursors.WaitCursor

    If objConn.State = ConnectionState.Open Then
        'do nothing
    Else
        strConnString = "Dsn=PostgreSQL35W;database=OpenERP;server=localhost;port=5432;uid=openpg;pwd=openpgpwd"
        objConn.ConnectionString = strConnString
        objConn.Open()
    End If


    If Me.txtImportDate.Text = "" Then
        MsgBox("Import Date field cannot be blank.")
        Exit Sub
    End If

    Dim str_import_date As String = Me.txtImportDate.Text


    strSQL = "SELECT * FROM product_template where import_date = " & "'" & str_import_date & "'"

    dtAdapter.SelectCommand = objCmd

    With objCmd
        .Connection = objConn
        .CommandText = strSQL
        .CommandType = CommandType.Text
        .ExecuteNonQuery()

        dtAdapter.Fill(ds, "product_template")

        iRecCount = ds.Tables("product_template").Rows.Count

    End With

    If iRecCount = 0 Then
        MsgBox("No records found.")
        Me.Cursor = System.Windows.Forms.Cursors.Default
        Exit Sub
    End If


    Dim cb As New Odbc.OdbcCommandBuilder(dtAdapter)


    'change the name field to item_description
    With ds
        For i As Integer = 0 To .Tables("product_template").Rows.Count - 1

            'this works, returns a string
            Dim str_default_code As String = (.Tables(0).Rows(i).Item("name").ToString)
            'this works
            Dim str_item_description As String = (.Tables(0).Rows(i).Item("description").ToString)

            .Tables("product_template").Rows(i).Item("name") = str_item_description
            'setting the variable doesn't work either - Dim str_item_description As String = "BH LITE BRT"

            'this throws the error
            dtAdapter.Update(ds, "product_template")

        Next
    End With

    Me.Cursor = System.Windows.Forms.Cursors.Default
End Sub

ERROR:  cross-database references are not implemented: "Rockefeller_ApplicationDb.public.userrolemapping"
LINE 4:         INNER JOIN "Rockefeller_ApplicationDb".public.userro..

SELECT   Department.nDeptID 
    FROM Users INNER JOIN Permission 
         ON Users.nUserID = Permission.nUserID INNER JOIN UserRoleMapping
         ON Users.nUserID = UserRoleMapping.nUserID INNER JOIN Department
         ON Permission.nDeptInst = Department.nInstID
         AND  Department.nInstID = 60
    WHERE     
         Users.nUserID = 3;

SELECT dep.ndept_id 
        FROM "Rockefeller_SecurityDb".public.users as  u 
        INNER JOIN  "Rockefeller_SecurityDb".public.permissions p ON u.nuser_id = p.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.userrolemapping as urm ON u.nuser_id = urm.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.department dep ON p.ndept_inst = dep.ninst_id
           AND  dep.ninst_id = 60
                        WHERE     
                            u.nuser_id = 3;

4 ответа

create extension dblink;

SELECT dblink_connect('otherdb','host=localhost port=5432 dbname=otherdb user=postgres password=???? options=-csearch_path=');

SELECT * FROM dblink('otherdb', 'select field1, field2 from public.tablex')
AS t(field1 text, field2 text);

CREATE EXTENSION postgres_fdw;

CREATE SERVER localsrv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'otherdb', port '5432');

CREATE USER MAPPING FOR <local_user>
SERVER localsrv
OPTIONS (user 'ohterdb_user', password 'ohterdb_user_password');

IMPORT FOREIGN SCHEMA public
FROM SERVER localsrv 
INTO public;

Я пытаюсь преобразовать запрос внутреннего соединения SQL в запрос внутреннего соединения PostgreSQL. В этом внутреннем запросе соединения, в котором используются таблицы, все таблицы не присутствуют в одной базе данных. мы разделили таблицы на две базы данных, например, приложение db и security db

1. пользователи и таблица разрешений присутствуют в security db
2. userrolemapping и отдел присутствуют в приложении db

Я пробовал, как показано ниже, но я получаю следующую ошибку

ошибка

ERROR:  cross-database references are not implemented: "Rockefeller_ApplicationDb.public.userrolemapping"
LINE 4:         INNER JOIN "Rockefeller_ApplicationDb".public.userro..

Сохраненная функция SQL

SELECT   Department.nDeptID 
    FROM Users INNER JOIN Permission 
         ON Users.nUserID = Permission.nUserID INNER JOIN UserRoleMapping
         ON Users.nUserID = UserRoleMapping.nUserID INNER JOIN Department
         ON Permission.nDeptInst = Department.nInstID
         AND  Department.nInstID = 60
    WHERE     
         Users.nUserID = 3;

Сохраненная функция PostgreSQL

SELECT dep.ndept_id 
        FROM "Rockefeller_SecurityDb".public.users as  u 
        INNER JOIN  "Rockefeller_SecurityDb".public.permissions p ON u.nuser_id = p.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.userrolemapping as urm ON u.nuser_id = urm.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.department dep ON p.ndept_inst = dep.ninst_id
           AND  dep.ninst_id = 60
                        WHERE     
                            u.nuser_id = 3;