PostgreSQL Schema – Identify Function Calls Across Databases

postgresqlschema

I have a database with 3 schemas (db1) where each schema calls the “guardarTransaccion” function of a database without schema (db2).

When calling the “guardarTransaccion” function pass the record id and the db2 has to connect to the corresponding schema using DBLINK and load the detail data (eg db1.schema1).

The problem I have is when we call the function from a schema in db1 I dont have a way to identify which schema I called the function for to load the corresponding data.

The code of the function in DB2 is:

CREATE OR REPLACE FUNCTION "public"."guardarTransaccion"("tipo" text, "idRemito" int4, "idPicking" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
DECLARE numeroRemito TEXT;
BEGIN
SET search_path = 'public', 'public', 'pg_catalog';


    -- load db1 data using dblink view
    IF "tipo" = 'E' THEN
        UPDATE picking SET rom_id = "idRemito", pk_remitado = 1, pk_cerrado = 1 WHERE pk_id = "idPicking";
        SELECT rmv_numero_comprobante INTO numeroRemito FROM romaneos_venta WHERE rmv_id = "idRemito";
    ELSE 
        UPDATE picking SET rmv_id = "idRemito", pk_remitado = 1, pk_cerrado = 1 WHERE pk_id = "idPicking";
        SELECT rmv_numero_comprobante INTO numeroRemito FROM remitos_venta WHERE rmv_id = "idRemito";
    END IF;

    -- operaciones con los datos / Operations with data


RETURN 1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

The way we find it to solve it is to create schema in the db2 only with the changes to each db1 schema, but it does not seem correct, what would be the best way to solve this?

Best Answer

You can get the current schema by using the System Information Functions

current_schema[()] returns text: name of current schema
current_schemas(boolean) returns text[]: names of schemas in search path, optionally including implicit schemas

I don't think dblink passes the context of the functions executed from the original database. So you'll have to wrap those functions and explicitly pass the context, perhaps by making the function variadic

CREATE OR REPLACE FUNCTION public.guardarTransaccion("tipo" text, "idRemito" int4, "idPicking" int4,
  context DEFAULT '{}'::text[] )

Then call it like the context explicitly. I would probably just think of a better way to engineer the system.

Related Solutions

Mysql – How toNSERT row in another database/schema in MySQL

Fully qualify the table name:

insert into SCHEMA_2.TABLE_2 values( ... );

SQL Server – How to Get Schema ID in Another Database Without Dynamic SQL

To answer the question literally, no there isn't a way to do this in T-SQL without using dynamic SQL (but see Aaron's suggestion to use a view). You can, however, use a SQLCLR function (which can be called from a T-SQL function). The following is demo code to illustrate the principle:

-- Returns 7 from any database context
SELECT dbo.GetSchemaID (N'AdventureWorks2012', N'Production');

T-SQL script:

CREATE ASSEMBLY [CLR] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004815C9500000000000000000E00002210B010800000A000000060000000000006E2900000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001829000053000000004000009002000000000000000000000000000000000000006000000C000000802800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007409000000200000000A000000020000000000000000000000000000200000602E72737263000000900200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050290000000000004800000002000500042100007C07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040084000000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A07176F0900000A0772350000700203280A00000A6F0B00000A076F0C00000A72BE0000701E6F0D00000A186F0E00000A076F0F00000A26076F0C00000A166F1000000A6F1100000AA5120000010CDE14072C06076F1200000ADC062C06066F1200000ADC082A011C000002001700576E000A0000000002000B006D78000A000000001E02281300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC010000237E0000380200009002000023537472696E677300000000C8040000C80000002355530090050000100000002347554944000000A0050000DC01000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000002000000130000000400000001000000010000000200000000000A0001000000000006003700300006007B0068000B008F0000000600BE009E000600DE009E000A0027010C010A0052013C010A00730160010A0085013C010A009F0160010A00A90100010600C50130000A00E3013C010A0009023C010A00160200010A00240260010A003002000106007402300006007A0230000000000001000000000001000100010010001200000005000100010050200000000096003E000A000100FC200000000086184A0010000300000001005000000002005D0011004A00140021004A001A0029004A00100031004A00100039004A00670141008001100049004A001000490090016C015100B50172016100CC0178015100D30167014900FA017F01690020028401810043028C0151005102920169006102960181006A029C0199008602100009004A001000200023001F002E000B00A8012E001300B1012E001B00BA01A001048000000000000000000000000000000000FC000000020000000000000000000000010027000000000002000000000000000000000001000001000000000000003C4D6F64756C653E00434C522E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A65637400476574536368656D614944002E63746F720044617461626173654E616D6500536368656D614E616D650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434C520053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E004462436F6D6D616E6400436F6D6D616E6454797065007365745F436F6D6D616E645479706500537472696E6700466F726D6174007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E00457865637574654E6F6E5175657279006765745F4974656D006765745F56616C756500496E7433320049446973706F7361626C6500446973706F7365000000003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D0020007400720075006500008087530045004C00450043005400200040006900640020003D0020005B0073006300680065006D0061005F00690064005D002000460052004F004D0020007B0030007D002E007300790073002E0073006300680065006D006100730020005700480045005200450020005B006E0061006D0065005D0020003D00200027007B0031007D0027003B000107400069006400000000088C123D0CFB464594BEC04885D418C10008B77A5C561934E089050002080E0E0320000105200101110D04200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E6973746963005402094973507265636973650154557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737301000000042001010E05200101121D05200101112D0600030E0E1C1C042000123507200212390E113D052001011145032000080520011239080320001C070703121D1225080801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000004815C95000000000020000007A0000009C2800009C0A000052534453FB53DD6509D3B74AAE81824B93BABACF01000000633A5C55736572735C5061756C2057686974655C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C4461746162617365365C4461746162617365365C6F626A5C52656C656173655C434C522E7064620000004029000000000000000000005E2900000020000000000000000000000000000000000000000000005029000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000340200000000000000000000340234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00494010000010053007400720069006E006700460069006C00650049006E0066006F0000007001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000030000800010049006E007400650072006E0061006C004E0061006D006500000043004C0052002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000003800080001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C0052002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000703900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[GetSchemaID] (@DatabaseName [nvarchar](4000), @SchemaName [nvarchar](4000))
RETURNS [int]
AS EXTERNAL NAME [CLR].[UserDefinedFunctions].[GetSchemaID];

C# source:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        IsDeterministic=false, 
        IsPrecise=true, 
        SystemDataAccess=SystemDataAccessKind.Read
        )
    ]
    public static int GetSchemaID(string DatabaseName, string SchemaName)
    {
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = String.Format("SELECT @id = [schema_id] FROM {0}.sys.schemas WHERE [name] = '{1}';", DatabaseName, SchemaName);
                cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                return (int)cmd.Parameters[0].Value;
            }
        }
    }
}

Best Answer

Related Solutions

Mysql – How toNSERT row in another database/schema in MySQL

SQL Server – How to Get Schema ID in Another Database Without Dynamic SQL

Related Question