Solid12
IEM- PostgreSQL. , , ( ).
, Oracle, PostgreSQL.
, , :
- , , ( ). SQL-.
- . Solid12 , .
- . , .
- PostgreSQL , Oracle. Solid12 , Oracle, .
- PL/PgSQL , PL/SQL .
- (.. ). , SELECT * FROM USERS, USERS , . PostgreSQL , Oracle.
- , PostgreSQL ( Oracle ). , .
- , NULL. Oracle, PostgreSQL varchar text NULL NULL. , .
- DDL PostgreSQL . , , (TRUNCATE) . . .
- , . CREATE OR REPLACE FUNCTION : (DROP) .
- , , ROLLBACK. (SAVEPOINT), . , ( , , ).
- DataReader- ( MARS Multiple Active Result Sets). , , , .
. SQL-, . , , , Oracle PostgreSQL.
- , .
PL/PgSQL
, (EXECUTE sql). , . , - .
, , . , search_path, , , .
.
, :
- set search_path to (current schema name)
- .
(PostgreSQL ), - .
PL/PgSQL, :
-- current search_path = my_schema
create or replace function my_func(my_arg text) returns void as $$
declare v_id bigint;
begin
perform another_func(my_arg); -- same as perform my_schema.another_func(my_arg);
select id into v_id from kernel.users -- table name is qualified with kernel schema name where login = my_arg; -- the rest is skipped...
end $$ language plpgsql set search_path to my_schema;
,
PostgreSQL , Oracle AUTHID CURRENT_USER ( Oracle AUTHID DEFINER).
Oracle, security option :
create or replace function my_secure_func() returns void as $$
begin -- call here any functions available to the superuser
end $$ language plpgsql security definer; -- default is security invoker
Oracle
PostgreSQL . :
- Oracle , , .
- PostgreSQL . , , . PostgreSQL , .
- Oracle , .
- PostgreSQL , .
pack_temp Oracle. :
create_permanent_temp_table(table_name [, schema_name]);
drop_permanent_temp_table(table_name [, schema_name]);
:
- PostgreSQL ( , ).
- create_permanent_temp_table, :
create temporary table if not exists another_temp_table ( first_name varchar, last_name varchar, date timestamp(0) with time zone, primary key(first_name, last_name) ) on commit drop;
-- create my_schema.another_temp_table select pack_temp.create_permanent_temp_table('another_temp_table', 'my_schema');
-- or create another_temp_table in the current schema -- select create_permanent_temp_table('another_temp_table');
-- don't forget to commit: PostgreSQL DDL is transactional commit;
, -- Oracle. drop_permanent_temp_table.
DataReader-
PostgreSQL: DataReader .
, .
, LINQ- SQL- . :
- LINQ- ( ). DataReader, . , ( ). :
//
var query = from a in DataContext.GetTable()
where a.ID = Constants.TestAgentID select a;
//
var testAgentId = Constants.TestAgentID;
var query = from a in DataContext.GetTable()
where a.ID = testAgentId select a;
- LINQ- , , LINQ SQL. : , . :
//
foreach (var langId in DataContext.GetTable().Select(x => x.ID))
{
using (LanguageService.UseLanguage(langId))
{
// do something language-specific
}
}
//
foreach (var langId in DataContext.GetTable().Select(x => x.ID).ToIDList())
{
using (LanguageService.UseLanguage(langId))
{
// do something language-specific
}
}
- ToArray/ToList/ToIDList LINQ-. , :
//
var dictionary = DataContext.GetTable().Where(d => dates.Contains(d.DT)) .GroupBy(g => g.DT, e => e.StatusID) .ToDictionary(k => k.Key, e => e.ToIDList());
//
var dictionary = DataContext.GetTable() .Where(d => dates.Contains(d.DT)) .GroupBy(g => g.DT, e => e.StatusID) .ToDictionary(p => p.Key);
var dict = dictionary.ToDictionary(p => p.Key, p => p.Value.ToIDList());
, . LINQ- , , DataReader- .
?
PostgreSQL. , , , PostgreSQL.
, PostgreSQL, , .
, light Solid12 enteprise Ultimate Solid , .
https://habrahabr.ru/post/329676/
