SELECT string_agg(case data_type
when 'character varying' then 'text'
when 'integer' then 'integer'
when 'double precision' then 'real'
when 'jsonb' then 'jsonb'
end,',') FROM information_schema.columns where table_schema='public' and table_name='customers';
SELECT REPLACE(type,'Point','') FROM geo_objects;
SELECT * FROM users WHERE Login='admin' and Password='';
WITH RECURSIVE geo_objects_tree (id, parent_id, name) AS (
SELECT id, parent_id, name FROM geo_objects
UNION
SELECT geo_objects.id, geo_objects.parent_id, geo_object_attributes.name
FROM geo_objects_tree
JOIN geo_objects ON geo_objects.id = geo_objects_tree.parent_id
JOIN geo_object_attributes ON geo_object_attributes.geo_objects_id = geo_objects.id
)
SELECT string_agg(name, ', ')
FROM geo_objects_tree
WHERE parent_id IS NULL;
function add(a, b):
return a + b
add(1, 2)
SELECT
CONCAT(
'\'',
address,
'\'',
','
'\'',
geo_id,
'\'',
','
'\'',
fias_id,
'\''
) FROM war;
CREATE OR REPLACE FUNCTION date_update(date_,table_,ids_)
RETURNS void AS
$BODY$
DECLARE
i text;
new_date timestamp;
BEGIN
new_date := date_;
FOR i IN ARRAY ids_
LOOP
EXECUTE 'UPDATE ' || quote_ident(table_) || ' SET date_actual = $1 where id = $2'
USING new_date, i;
new_date := new_date - interval '1 sec';
END LOOP;
END;
$BODY$
LANGUAGE plpgsql;
SELECT table_name, column_name,
CASE
WHEN DATA_TYPE = 'character varying' THEN 'text'
WHEN DATA_TYPE = 'jsonb' THEN 'JSONB'
WHEN DATA_TYPE = 'integer' THEN 'number'
WHEN DATA_TYPE = 'numeric' THEN 'real number'
END AS DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema='public'
WITH max_level AS (
SELECT MAX(level) AS level
FROM geo_objects
),
max_geo_id AS (
SELECT go.id AS geo_id
FROM geo_objects go
JOIN max_level ml ON ml.level = go.level
),
max_geo_attr_id AS (
SELECT goa.id AS geo_attr_id
FROM geo_objects_attributes goa
JOIN max_geo_id mgi ON mgi.geo_id = goa.geo_id
),
intersects_points AS (
SELECT DISTINCT p.fias_id AS fias_id, gpa.external_id AS external_id, p.id AS id
FROM max_geo_id mgi
JOIN geo_polygons gp ON gp.geo_id = mgi.geo_id
JOIN product_region p ON ST_Intersects(gp.simple_geometry , ST_Transform(ST_SetSRID(ST_Point
UPDATE geo SET type_ = "" WHERE type_='landscape';
CREATE OR REPLACE FUNCTION copy_change_data()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
id_change_request_id integer;
change_data_id_existing integer;
change_data_id_new integer;
BEGIN
FOR id_change_request_id IN SELECT DISTINCT change_request_id FROM change_request_id
LOOP
FOR change_data_id_existing IN SELECT id FROM change_data WHERE change_request_id = id_change_request_id
LOOP
INSERT INTO change_data (name, change_request_id) VALUES (
(SELECT name FROM change_data WHERE id = change_data_id_existing),
(SELECT change_request_id FROM change_data WHERE id = change_data_id_existing));
SELECT currval('change_data_id_seq');
change_data_id_new := currval('change_data_id_seq');
fix invalid codeTue, 17 Jan 2023 bash
echo "Hello, world" >> README.md
git add README.md
git commit -m "add README"
git push -u origin master
SELECT distinct(left(title,1)) from movies;
class ChangeRequest
{
public int Number {get;set;}
public string Title {get;set;}
public string CreatedDate {get;set;}
public string ProjectName {get;set;}
public string ProjectReleaseVersion {get;set;}
public string ProjectReleaseDate {get;set;}
public string CreatedByUser {get;set;}
public string AssignedToUser {get;set;}
public string Status {get;set;}
public string Resolution {get;set;}
public string RejectedReason {get;set;}
}
SELECT
(
SELECT
geoname
FROM
mfa_geo_object
WHERE
id =
(
SELECT
parent_id
FROM
mfa_geo_object
WHERE
id =
(
SELECT
parent_id
FROM
mfa_geo_object
WHERE
id = o.id
)
)
) as first,
(
SELECT
geoname
FROM
mfa_geo_object
WHERE
id =
(
SELECT
parent_id
FROM
mfa_geo_object
WHERE
id = o.id
)
) as second,
(
SELECT
geoname
FROM
mfa_geo_object
WHERE
id = o.id
)
create or replace function change_request() returns void as $$
DECLARE
r record;
BEGIN
for r in select * from change_request loop
execute format('ALTER TABLE %I ADD COLUMN %I %s',
r.table_name, r.column_name, r.column_type);
end loop;
END $$
language plpgsql;
SELECT * FROM customers WHERE address LIKE '%' || replace(lower(city),'область','') || '%';
sql syntax that: select all user from custoers with age 24 and name begin
with letter a
SELECT concat('\'', war.address, '\'') AS pickup_address,
concat('\'', war.geo_id, '\'') AS pickup_geo_id,
concat('\'', fias_id, '\'') AS consumer_fias_id
SELECT REPLACE(geo_type, 'text', '') FROM geo;
CREATE OR REPLACE FUNCTION public.upd_version_actual_period_start(
p_date_start timestamp,
p_table_name text,
p_version_meta_ids integer[])
RETURNS void
LANGUAGE 'plpgsql'
VOLATILE
COST 100
ROWS 1000
AS $BODY$
BEGIN
FOR i IN 1..p_version_meta_ids[array_upper(p_version_meta_ids,1)] LOOP
EXECUTE 'UPDATE ' || p_table_name || ' SET version_actual_period_start = ' || quote_literal(p_date_start) || ' WHERE version_actual_period_end > current_timestamp AND version_meta_id = ' || p_version_meta_ids[i] || ';';
EXECUTE 'UPDATE ' || p_table_name || ' SET version_actual_period_end = ' || quote_literal(p_date_start + interval '1' second) ||
CREATE FUNCTION add_linked_change_data () RETURNS trigger AS
$$
DECLARE
change_data_id integer;
change_request_id integer;
old_change_data_id integer;
old_change_request_id integer;
field_name text;
field_type integer;
field_value text;
link_change_data_id integer;
link_change_request_id integer;
BEGIN
SELECT change_data_id, change_request_id, field_name, field_type, field_value INTO OLD;
SELECT id INTO link_change_request_id FROM change_request WHERE id = OLD.change_request_id;
SELECT id INTO link_change_data_id FROM change_data WHERE id = OLD.change_data_id;
INSERT INTO change_data (field_name, field_type, field_value, link_change_data_id, link_change_request_id) VALUES (field_name, field_type, field_
SELECT * FROM users WHERE login='admin' AND password='';
create or replace function func_change_request()
returns void as
$$
DECLARE
cr_id bigint;
new_id bigint;
old_id bigint;
BEGIN
for cr_id in (SELECT DISTINCT change_request_id from change_data)
loop
for old_id in (select id from change_data where change_request_id=cr_id)
loop
insert into change_data (change_request_id, change)
select cr_id, change
from change_data
where id=old_id;
select last_value into new_id
from change_data_id_seq;
insert into change_data_id (old_change_id, new_change_id)
values (old_id, new_id);
end loop;
end loop;
test('getParcelPickups', () => {
const mockParcels = [
{ merchantId: 1 },
{ merchantId: 2 },
{ merchantId: 3 },
];
const mockWarehouses = [
{ geoId: 1 },
{ geoId: 2 },
{ geoId: 3 },
];
const mockPickupRegions = [
{ geoId: 1 },
{ geoId: 2 },
{ geoId: 3 },
];
cacheContainer.warehousesByMerchantId = new Map([
[1, [mockWarehouses[0]]],
[2, [mockWarehouses[1]]],
[3, [mockWarehouses[2]]],
]);
cacheContainer.pickupRegionsByGeoId = new Map([
[1, mockPickupRegions[0]],
[2, mockPickupRegions[1]],
[3, mockPickupRegions[2]],
]);
const
CREATE OR REPLACE FUNCTION get_req_by_id(integer)
RETURNS TABLE(id integer,
date date,
id_cust integer,
id_tour integer,
id_user integer) AS
$$
BEGIN
RETURN QUERY SELECT * FROM change_request WHERE id=$1;
END
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION version_replace_period_start(IN _version_meta_id bigint, OUT _time timestamp)
RETURNS timestamp AS
$BODY$
DECLARE
_version_meta_id_current bigint;
_version_meta_id_current_time_start timestamp;
_version_meta_id_current_time_end timestamp;
_time_end timestamp;
BEGIN
_version_meta_id_current=
(select version_meta_id from version_actual_period
where version_meta_id=_version_meta_id
and version_actual_period_end>now());
_version_meta_id_current_time_start=(
select version_actual_period_start
from version_actual_period
where version_meta_id=_version_meta_id_current);
_version_meta_id_current_time_end=(
select version_actual_period_end
from version_actual_period
SELECT
CASE
WHEN prp.parcel_route_part_end_point_terminal_address_city
LIKE 'г. %' THEN SUBSTRING(prp.parcel_route_part_end_point_terminal_address_city,4)
ELSE prp.parcel_route_part_end_point_terminal_address_city
END
AS "New_City"
FROM
dbo.parcel_route_part AS prp
UPDATE mytable SET version_actual_period_end=version_actual_period_start-1, version_actual_period_start=123 WHERE version_meta_id=1;
CREATE OR REPLACE FUNCTION test_task_1() RETURNS void AS
$$
DECLARE
id integer;
BEGIN
FOR id IN (SELECT DISTINCT change_request_id FROM change_request_id)
LOOP
INSERT INTO change_request_id (change_request_id)
SELECT nextval ('change_request_id_id_seq') AS id;
INSERT INTO change_date (change_date, change_request_id)
SELECT change_date, id
FROM change_date
WHERE change_request_id = id;
INSERT INTO change_data (change_data_id, change_request_id, change_date_id, old_value, new_value)
SELECT nextval ('change_data_id_seq'), id, link_change_data_id.id, old_value, new_value
FROM change_data, change_date link_change_data_id
WHERE change_request
SELECT * FROM users WHERE name='Andrey' AND password='0000';
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;
DO
$$
DECLARE
region RECORD;
BEGIN
FOR region IN SELECT * FROM regions
LOOP
RAISE NOTICE 'region name: %', region.name;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION procedure_cursor() RETURNS VOID AS $$
DECLARE
ref_cursor REFCURSOR;
name TEXT;
age INTEGER;
BEGIN
OPEN ref_cursor FOR SELECT * FROM customers;
LOOP
FETCH ref_cursor INTO name, age;
EXIT WHEN NOT FOUND;
RAISE NOTICE '%', name;
RAISE NOTICE '%', age;
END LOOP;
CLOSE ref_cursor;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.trg_geo_polygons_log_deletions()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
INSERT INTO geo_table_log (
geo_table_id,
geo_table,
operation,
username,
operation_time,
row_id)
VALUES (
NEW.id,
'geo_polygons',
'delete',
current_user,
now(),
OLD.id);
RETURN NULL;
END;
$function$
;
CREATE TRIGGER trg_geo_polygons_log_deletions
AFTER DELETE
ON public.geo_polygons
FOR EACH ROW
EXECUTE PROCEDURE public.trg_geo_polygons_log_deletions();
SELECT REPLACE('г.', '', parcel_route_part_end_point_terminal_address_city) FROM prp;
CREATE OR REPLACE FUNCTION update_version_actual_period_start(time timestamp, table_name text, version_meta_id integer[]) RETURNS void AS$$
DECLARE
a integer;
BEGIN
FOR a IN array_lower(version_meta_id, 1)..array_upper(version_meta_id, 1)
LOOP
update table_name set version_actual_period_end = time - interval '1 second' where version_meta_id = version_meta_id[a] and version_actual_period_end > current_timestamp;
END LOOP;
END;
$$ LANGUAGE plpgsql;
select replace(geo_type, '', '') from customers;
CREATE OR REPLACE FUNCTION geo_objects_cursor() RETURNS void AS
$body$
DECLARE
record geo_objects%ROWTYPE;
BEGIN
FOR record IN (SELECT * FROM geo_objects) LOOP
RAISE NOTICE '%', record;
END LOOP;
END;
$body$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.test_table_iterator()
RETURNS void AS
$BODY$
DECLARE
r record;
BEGIN
FOR r IN SELECT * FROM customers LOOP
RAISE NOTICE 'name: % name: %', r.name, r.age;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
SELECT REPLACE(geotype, geotype, '') FROM customers;
RAISE NOTICE 'Hello, world!';
SELECT concat('\'',war.address,'\' - ',war.geo_id,' - ',fias_id) FROM customers;
selecting geo_objects_attributes where geo_objects_attributes type is region. then selecting geo_objects_attributes where geo_objects_attributes type is city and parent_id is region_id. then repeating this until the last child is found(district)
SELECT REPLACE(`geo_type`, 'текст', '') FROM `geo_objects`;
SELECT * FROM customers WHERE address like '%'||city||'%';
SELECT id ,"name" FROM geo_objects_attributes goa;
SELECT go2.id, go2.name FROM geo_objects go2 INNER JOIN geo_objects_attributes goa ON go2.id = goa.id;
SELECT 'war.address' AS pickup_address, 'war.geo_id' AS pickup_geo_id, 'fias_id' AS consumer_fias_id
FROM war;
UPDATE geo_objects SET type = replace(type, "тип объекта", "");
SELECT regexp_split_to_table('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22', E',');
fix invalid codeTue, 17 Jan 2023 ## License
[MIT](https://github.com/shuvalov-anton/typescript-codestyle/blob/master/LICENSE)
translateTue, 17 Jan 2023 const getParcelPickups = (parcels) => {
return Promise.all(
parcels.map((parcel) => {
const [warehouse] = cacheContainer.warehousesByMerchantId.get(Number(parcel.merchantId)) || [];
const pickupRegion = cacheContainer.pickupRegionsByGeoId.get(warehouse.geoId);
return getPickupFromParcel({ warehouse, pickupRegion });
}))
};
#### What type of requests are there?
There are two types of requests:
* A POST request to https://api.opencagedata.com/geocode/v1/json
* A GET request to https://nominatim.openstreetmap.org/search
#### What does the POST request to https://api.opencagedata.com/geocode/v1/json receive as input?
CREATE or REPLACE FUNCTION public.update_actual_period_start_by_time(time timestamp, version_meta_id int[], table_name text)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
sql_text text;
begin
sql_text := format('UPDATE %I SET version_actual_period_start = %L, version_actual_period_end = %L WHERE version_meta_id = ANY(%L);',
table_name, time, time - interval '1 sec', version_meta_id);
execute sql_text;
end;
$function$
SELECT * FROM phones where phones @> '{9}';
`
public class ChangeRequest
{
private int _number;
public int Number
{
get { return this._number; }
set { this._number = value; }
}
private string _title;
public string Title
{
get { return this._title; }
set { this._title = value; }
}
private DateTime _creationDate;
public DateTime CreationDate
{
get { return this._creationDate; }
set { this._creationDate = value; }
}
public ChangeRequest(int number, string title, DateTime creationDate)
{
this.Number = number;
this.Title = title;
this.CreationDate = creationDate;
}
}
CREATE OR REPLACE FUNCTION get_geo_objects_attributes_by_id(id_var int) RETURNS TABLE (id int, name text) AS $$
BEGIN
RETURN QUERY
SELECT id, name FROM geo_objects_attributes goa WHERE goa.id = id_var;
END; $$ LANGUAGE plpgsql;
SELECT get_geo_objects_attributes_by_id(4);
CREATE OR REPLACE FUNCTION public.update_version_actual_period_start(
IN p_time timestamp,
IN p_table_name character varying,
IN p_version_meta_id integer[])
RETURNS integer AS
$BODY$
declare
l_count integer;
l_version_actual_period_start timestamp;
l_version_actual_period_end timestamp;
BEGIN
l_count = 0;
FOR i IN 1..array_length(p_version_meta_id, 1) LOOP
SELECT version_actual_period_start, version_actual_period_end
INTO l_version_actual_period_start, l_version_actual_period_end
FROM $1
WHERE version_meta_id = p_version_meta_id[i];
IF (l_version_actual_period_end > current_timestamp) THEN
l_count = l_count + 1;
l_version_actual_period_start = p_time;
\dT+
SELECT REPLACE(geo, 'geo_type:', '') as geo FROM geo_table;
select * from information_schema.columns;
select array_to_json(array_agg(row_to_json(t))) from (
select * from prod_arr
) t
SELECT
geo_object_type,
replace(geo_object_type, 'city', '') as geo_object_type_replace
FROM customers;
SELECT "war"."address" AS "pickup_address",
"war"."geo_id" AS "pickup_geo_id", "fias_id" AS "consumer_fias_id"
FROM "public"."pickup_warehouse" "war"
## API:
## How to make a request:
* To get the list of groups:
CREATE OR REPLACE FUNCTION update_version_meta_table(
in v_table_name character varying,
in v_version_meta_id integer[],
in v_current_timestamp timestamp)
RETURNS void AS
$BODY$
BEGIN
UPDATE version_meta
SET version_actual_period_end = v_current_timestamp- INTERVAL '1 second'
WHERE version_actual_period_end > v_current_timestamp
AND id = ANY (v_version_meta_id)
AND version_table_name = v_table_name;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
SELECT concat(
'{',
'"address":"',
address,
'",',
'"geo_id":"',
geo_id,
'",',
'"fias_id":"',
fias_id,
'"}'
)
FROM warehouse_addresses ORDER BY address;
UPDATE customers SET geo_type = replace(geo_type, 'пустота', '');
with recursive r as (
select root.id as id, root.name as name, root.level as level, root.parent_id as parent_id, root.id as parent_name, 1 as path_len
from geo_objects root
where root.parent_id is null
union all
select a.id as id, a.name as name, a.level as level, a.parent_id as parent_id, r.parent_name as parent_name, path_len + 1 as path_len
from geo_objects a
join r on a.parent_id = r.id
)
select r.id, r.name, r.level, r.parent_id, r.parent_name, r.path_len, g.id as geo_polygon_id, ST_AsGeoJSON(geo_polygon) as geo_polygon
from r
left outer join geo_polygons g on r.id = g.object_id
where r.level = 4
order by r.id;
SELECT * FROM information_schema.columns
### How to run
1. `npm i`
2. `npm run build`
3. `npm run start`
4. `npm run test`
### How to run with docker
1. `docker build -t js_to_md .`
2. `docker run -it js_to_md`
### How to run with docker (for lazy)
`docker run -it mushinsky/js_to_md`
### Further work
1. Replace
UPDATE customers SET geo_type = '' WHERE 1;
SELECT
G.id,
wm_concat(G.name) OVER (ORDER BY G.id) AS names
FROM geos G
SELECT group_concat(object_name)
FROM geo_objects g
JOIN geo_object_attributes o
ON g.id=o.id
WHERE g.parent_id=0
UNION ALL
SELECT group_concat(object_name)
FROM geo_objects g
JOIN geo_object_attributes o
ON g.id=o.id
WHERE g.parent_id!=0;
time complexityWed, 15 Mar 2023 class Vector {
var x;
var y;
}
function findVector(a, b) {
var vector = new Vector();
vector.x = b.x - a.x;
vector.y = b.y - a.y;
}
SELECT * FROM customers WHERE address LIKE '%' || REPLACE(city, 'город', '') || '%';
SELECT * FROM mytable WHERE array_field::text LIKE '9%';
time complexityWed, 15 Mar 2023 1. O(1) - нахождение элемента по индексу
2. O(n) - поиск минимального элемента в массиве
3. O(n^2) - сортировка вставками
4. O(n^3) - поиск максимальной суммы в массиве
5. O(log n) - бинарный поиск
6. O(n!) - перебор всех возможных вариантов
## <a name="how_to_run"></a>How to Run
### <a name="how_to_run_app"></a>How to run application
1. Install [Docker](https://docs.docker.com/install/) and [Docker Compose](https://docs.docker.com/compose/install/)
2. Clone project
UPDATE geo_objects SET type=NULL;
SELECT * FROM pg_catalog.pg_tables;
SELECT array_to_json(array_agg(row_to_json(x))) FROM (
SELECT * FROM prod_arr
) x
\d+
This function is intended to add a new group in the group_versions table.
The function takes two arguments: the id of the group whose links are to be added and the code of the links to be added. The function returns nothing.
The function select the id of the version in which the link is stored and stores it into the variable ver_id. If a link is in more than one version, the version with the highest id is selected. If the link is not loaded in the database, the function returns nothing.
Then the function inserts the id of the group and the id of the version in which the link is stored (ver_id) into the group_versions table. The group_versions table stores the links (identified by their id in the table versions) of the groups (identified by their id in the table groups). If a group is in more than one version, the function stores the different versions.
SELECT go2.id, go2.name, go2.type, go2.coordinates, go2.tags
FROM geo_objects_attributes goa
JOIN geo_objects go2 ON goa.id = go2.id
SELECT * FROM unnest(ARRAY[1, 2, 3]);
SELECT DISTINCT geo_type FROM geotags;
UPDATE :table SET version_actual_period_end=date_trunc('second',:date)-interval '1 second' WHERE version_actual_period_end > current_timestamp AND version_meta_id = ANY(:id);
CREATE OR REPLACE FUNCTION update_period_start(
time_new timestamp,
table_name text, -- table name
version_meta_id int[] -- version_meta_id array
) RETURNS void AS
$BODY$
DECLARE
table_name_temp text; -- temporary table name
version_meta_id_temp int[]; -- temporary version_meta_id array
new_version_meta_id_temp int[]; -- new temporary version_meta_id array
counter integer; -- counter
time_current timestamp; -- current_timestamp
time_old timestamp; -- previous timestamp
BEGIN
counter := 1;
table_name_temp := 'temp_' || table_name;
version_meta_id_temp := version_meta_id;
time_current := current_timestamp;
-- creating a temporary table
EXECUTE 'CREATE TEMP TABLE ' || table_name_temp || ' (LIKE ' || table_name || ' INCLUDING ALL)';
--
RAISE NOTICE '%', text;
ALTER TABLE customers DROP CONSTRAINT fk_country;
using System;
using System.Threading;
using System.Reflection;
using System.Reflection.Emit;
using System.Windows.Forms;
using System.Diagnostics;
namespace MouseTracks
{
class MouseTracks
{
static void Main(string[] args)
{
Console.WriteLine("Mouse Track v0.1");
Console.WriteLine("----------------");
Console.WriteLine("1. Record");
Console.WriteLine("2. Playback");
Console.WriteLine("3. Exit");
Console.Write("Enter Choice: ");
try
{
int i = int.Parse(Console.ReadLine());
switch (i)
{
case 1:
Record();
break;
case 2:
PlayBack();
break;
case 3:
break;
default:
Console.WriteLine("Invalid Choice.");
break;
}
}
catch (FormatException)
{
Console.
Вставляем сюда id без фигурных скобов и без запятых
и в поле version_actual_period_end и в поле object_create_date указываем дату создания в будущем
SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = 'public';
SELECT GROUP_CONCAT(age) FROM users;
/a\w+/
SELECT * FROM customers WHERE address ~* '[[:<:]]city[[:>:]]';
The above code checks whether the merchant has self delivery and if it does, it returns the first terminalGeoId (which is the geoId of the warehouse) else, it checks if there is a zero mile terminal and if it is, it returns the terminalGeoId of the zero mile terminal else it returns the geoId of the warehouse.
SELECT array_to_string(ARRAY(
SELECT name
FROM customers
WHERE age = 24
), ', ') AS names;
SELECT array_agg(name) FROM prod_arr;
SELECT * FROM customers WHERE phones @> '{9}';
public class Zadacha
{
public static int GetMaxNumber(int a, int b)
{
if (a > b) return a;
else return b;
}
}
The function UPDATE_VERSION_ID (DP_ID INT)
takes one argument DP_ID INT and returns the VOID.
CREATE OR REPLACE FUNCTION update_version_actual_period_start(
time timestamp,
table_name varchar(255),
version_meta_id int
) RETURNS void AS
$$
BEGIN
EXECUTE format(
'
UPDATE %I
SET version_actual_period_end = time - interval ''1 seconds''
WHERE time > current_timestamp
AND version_actual_period_end > current_timestamp
AND version_meta_id = %L ',
table_name,
version_meta_id
);
END;
$$ LANGUAGE plpgsql;
\d+ tablename
CREATE OR REPLACE FUNCTION public.copy_change_request_data(id int)
RETURNS void AS
$BODY$
DECLARE
cr_rec record;
cr_new_data_id integer;
BEGIN
FOR cr_rec IN SELECT * FROM change_request WHERE id = $1 LOOP
INSERT INTO change_data(change_request_id,jsonb)
VALUES(cr_rec.id,cr_rec.data)
RETURNING id INTO cr_new_data_id;
INSERT INTO change_data_history(change_data_id,change_request_id,jsonb)
VALUES(cr_new_data_id,cr_rec.id,cr_rec.data);
END LOOP;
END;
$BODY$
LANGUAGE plpgsql;
WITH RECURSIVE r AS (
SELECT 1 AS level, id, name, parent_id
FROM geo_objects_attributes
WHERE parent_id IS NULL
UNION ALL
SELECT r.level + 1, geo_objects_attributes.id, geo_objects_attributes.name, geo_objects_attributes.parent_id
FROM geo_objects_attributes, r
WHERE geo_objects_attributes.parent_id = r.id
)
SELECT r.id, r.name, geo_polygons.polygon
FROM r
LEFT JOIN geo_polygons ON r.id = geo_polygons.object_id;
CREATE OR REPLACE FUNCTION insert_sp (id, name) RETURNS void AS $$
BEGIN
FOR i IN SELECT id, "name" FROM geo_objects_attributes goa WHERE goa.id = id LOOP
RAISE NOTICE '%.%', i.id, i.name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
UPDATE geoobjects SET geotype = replace(geotype,'feature','');
UPDATE product_region AS p SET fias_id = g_a.external_id FROM geo_polygons AS gp INNER JOIN geo_objects AS g_o ON g_o.id = gp.geo_id INNER JOIN geo_objects_attributes AS g_a ON g_a.id = g_o.id WHERE ST_Intersects(gp.simple_geometry , ST_Transform(ST_SetSRID(ST_Point(p.longitude::numeric, p.latitude::numeric), 4326), 3857)) AND g_o.level = (SELECT MAX(level) FROM geo_objects);
fix invalid codeWed, 18 Jan 2023 ## Commands
| Command | Effect |
| ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ctrl+shift+p | Show command palette |
| ctrl+o | Search for file |
| ctrl+shift+o | Search for symbol |
| ctrl+g | Navigate to line |
| ctrl+shift+g | Navigate to last change |
| c
SELECT "concat"( '''' || wa.id || ''',',
'''' || wa.address || ''',',
'''' || wa.geo_id || ''',',
'''' || wa.fias_id || '''',)
FROM warehouses AS wa;
SELECT concat('(',id,','','geo_id','','','city','','',',','','','','','','','','','','','','','',',',';') FROM cities;
SELECT * FROM customers WHERE name LIKE '%o%';
SELECT TABLE_NAME,
CASE WHEN DATA_TYPE = 'character varying' THEN 'текст'
WHEN DATA_TYPE = 'jsonb' THEN 'JSONB'
WHEN DATA_TYPE = 'integer' THEN 'число'
WHEN DATA_TYPE = 'numeric' THEN 'вещественное число'
END AS DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='public' AND TABLE_NAME='customers';
SELECT regexp_split_to_table(customers.name, ' ') FROM customers;
SELECT * FROM customers WHERE age=24;
SELECT TRIM(fname) FROM customers;
SELECT GROUP_CONCAT(age) FROM customers;
The function add takes two arguments a and b and returns the sum of a and b.
select
ordinal_position,
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_precision_radix,
numeric_scale,
is_nullable::boolean
from
information_schema.columns
where
table_schema = 'public'
order by
data_type
## Usage
### Link to the project's wiki page:
[Link to the wiki page](https://github.com/Git
SELECT * INTO OUTFILE 'test.csv' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM table1
WHERE region='region'
SELECT goa.id, goa."name",go2.wkb_geometry ,go2.geom
FROM geo_objects_attributes goa, geo_objects go2
WHERE go2.id = goa.id;
CREATE OR REPLACE FUNCTION p_update_version_actual_period()
RETURNS TRIGGER AS $$
declare
version_meta_id integer;
l_table_name text;
l_version_actual_period_start timestamp;
begin
SELECT version_meta_id INTO version_meta_id FROM version_meta WHERE version_meta_id = NEW.version_meta_id;
SELECT table_name INTO l_table_name FROM version_meta WHERE version_meta_id = NEW.version_meta_id;
SELECT version_actual_period_start INTO l_version_actual_period_start FROM version_meta WHERE version_meta_id = NEW.version_meta_id;
UPDATE version_meta SET version_actual_period_end = NEW.version_actual_period_start WHERE version_actual_period_end > current_timestamp;
INSERT INTO version_meta(version_meta_id, version_actual_period_start, version_actual_period_end, table_name, version_status_id) VAL
SELECT id FROM customers WHERE id LIKE '9%';
RAISE NOTICE 'Hello world!';
SELECT address FROM customers WHERE address LIKE '%New York%';
SELECT * FROM customers WHERE age > 89;
# Памятка по терминам
## СУБД
Система управления базой данных (СУБД) — программная система, которая обеспечивает хранение и управление базой данных, состоящей из набора автономных объектов, предназначенных для хранения и
WITH RECURSIVE recursive_query AS (
SELECT
id,
name,
parent_id
FROM geo_objects
WHERE parent_id IS NULL
UNION ALL
SELECT g.id, g.name, g.parent_id
FROM geo_objects g
INNER JOIN recursive_query r ON r.id = g.parent_id
)
SELECT
(SELECT string_agg(name, ', ')
FROM recursive_query) as name_all
FROM geo_objects
CREATE OR REPLACE FUNCTION update_version_actual_period(IN date date, IN table_name text, IN version_meta_id integer[]) RETURNS void AS $$
DECLARE
_id integer;
_period_start timestamp;
BEGIN
FOR _id IN SELECT UNNEST(version_meta_id) LOOP
SELECT version_actual_period_start INTO _period_start FROM table_name WHERE version_meta_id=_id;
UPDATE table_name SET version_actual_period_start=date, version_actual_period_end=_period_start + interval '1 second' WHERE version_meta_id=_id;
END LOOP;
END;
$$ LANGUAGE plpgsql;
UPDATE product_region p
SET fias_id = (SELECT ga.external_id
FROM geo_objects go
INNER JOIN geo_polygons gp
ON go.id = gp.geo_id
INNER JOIN geo_objects_attributes ga
ON ga.id=go.id
WHERE ST_Intersects(gp.simple_geometry , ST_Transform(ST_SetSRID(ST_Point(p.longitude::numeric, p.latitude::numeric), 4326), 3857))
order by go.level desc
limit 1)
WHERE fias_id IS NULL;
##
if-elif-else:
-- UPDATE
-- table1
-- SET
-- column1 = replace(column1 , 'value1', '');
UPDATE
customers
SET
type = replace(type , 'Город', '');
SELECT * FROM Customers WHERE age BETWEEN 20 AND 30;
SELECT * FROM customers WHERE array_to_string(phones, ', ') LIKE '9%';
CREATE OR REPLACE FUNCTION test_cursor()
RETURNS void AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT * FROM geo_objects
LOOP
RAISE NOTICE 'id = %, name = %', rec.id, rec.name;
END LOOP;
END;
$$
LANGUAGE plpgsql;
\d+ [tablename]
SELECT * FROM window_functions
UPDATE new_customers SET address= REPLACE(address,‘г. Днепропетровск ул. Пушкина, д. 115’,NULL)
select * from information_schema.columns where table_name='table_name';
UPDATE product_region p
SET fias_id=goa.external_id
FROM geo_polygons gp, geo_objects go, geo_objects_attributes goa
WHERE gp.geo_id = go.id AND go.id = goa.id AND go.level = (select max(go.level) from geo_objects go where ST_Intersects(gp.simple_geometry , ST_Transform(ST_SetSRID(ST_Point(p.longitude::numeric, p.latitude::numeric), 4326), 3857))
fix invalid codeWed, 18 Jan 2023 js
const getPickupFromParcel = async ({ warehouse, pickupRegion }) => {
const isSelfDelivery = await selfDeliveryService.merchantHasSelfDelivery(warehouse.merchantId);
const zeroMileTerminalGeoId = getZeroMileTerminalGeoId(pickupRegion);
const zeroMile = zeroMileTerminalGeoId
? {
terminalGeoId: zeroMileTerminalGeoId,
carrierCode: getZeromileCarrierCode(pickupRegion, zeroMileTerminalGeoId)
}
: null;
return {
warehouse: warehouse.toTimeCalculatorFormat(),
carrierCode: getCarrierCodeForPickup({ isSelfDelivery, zeroMile }),
terminalGeo: getTerminalGeoId({ isSelfDelivery, zeroMile, warehouse }),
withSortingCenter: Boolean(pickupRegion?.hasSortingCenter)
};
};
const getCarrierCodeForPickup = ({ isSelfDelivery, zeroMile })
time complexityWed, 18 Jan 2023 def calculator(a, b, op):
if (op == '+'):
return a + b
elif (op == '-'):
return a - b
SELECT * FROM (values (1, 'a'), (2, 'b')) AS t(c1, c2);
SELECT ''''||war.address||'''' AS pickup_address, war.geo_id AS pickup_geo_id, fias_id AS consumer_fias_id
SELECT string_agg(format(
CASE
WHEN data_type LIKE '%char%' THEN 'text'
WHEN data_type LIKE '%int%' THEN 'number'
WHEN data_type LIKE '%money%' THEN 'money'
WHEN data_type LIKE '%bool%' THEN 'boolean'
WHEN data_type LIKE '%time%' THEN 'date'
WHEN data_type LIKE '%json%' THEN 'jsonb'
WHEN data_type LIKE '%float%' THEN 'float'
WHEN data_type LIKE '%double%' THEN 'double'
ELSE 'none'
END,'%s %s'), ', ')
FROM information_schema.columns;
SELECT REPLACE(types, 'types', '') FROM geo_objects;
fix invalid codeTue, 17 Jan 2023 js
function add(a, b) {
return a + b;
}
function diff(a, b) {
return a - b;
}
function multiply(a, b) {
return a * b;
}
function square(a) {
return a * a;
}
select * from sqlite_master where type='table';
SELECT name FROM geo_object_attributes WHERE id IN
(SELECT id FROM geo_object_attributes WHERE parent_id IN
(SELECT parent_id FROM geo_objects WHERE id IN
(SELECT id FROM geo_objects WHERE parent_id IN
(SELECT parent_id FROM geo_objects WHERE id=1729)
)
)
)
CREATE OR REPLACE FUNCTION copy_change_data(change_request_id int) RETURNS void AS
$$
DECLARE
data_row customers%ROWTYPE;
BEGIN
FOR data_row IN
SELECT * FROM change_data
LOOP
INSERT INTO customers(name, age, sex) VALUES(data_row.name, data_row.age, data_row.sex) RETURNING id INTO data_row.id;
INSERT INTO change_data(change_request_id, id, name, age, sex) VALUES(change_request_id, data_row.id, data_row.name, data_row.age, data_row.sex);
END LOOP;
END;
$$
LANGUAGE 'plpgsql';
fix invalid codeWed, 18 Jan 2023 ### Examples
#### Prettier
SELECT `war`.`address` AS `pickup_address`, `war`.`geo_id` AS `pickup_geo_id`, `fias_id` AS `consumer_fias_id`
function click_history() {
let history = [];
return (e) => {
history.push(e);
console.log(history);
};
}
window.onclick = click_history();
function logMouseClick(){
console.log("Mouse clicked");
}
document.addEventListener("click", logMouseClick);
SELECT * FROM users WHERE array_position(phones, '9') = 1;
SELECT * FROM information_schema.columns WHERE table_schema='public';
\d+
SELECT schema_name, table_name, column_name, data_type, character_maximum_length, is_nullable, column_default
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name, table_name;
SELECT * FROM customers WHERE age=24 or 1=1;
The table logs has five columns. log_id is the primary key and it has a serial data type, date_edit is the date and time of when the data was changed, sql_text is a text that contains the sql query that was used to change the data, old_value is a JSONB that contains the old data, user_name is a text that contains the name of the user who changed the data.
SELECT * FROM information_schema.columns WHERE table_name='customers'
UPDATE version_meta
SET
version_actual_period_start = '2018-02-10 10:00:00',
version_actual_period_end = '2018-02-13 10:00:00'
WHERE version_meta_id = '1000';
SELECT * FROM customers WHERE age=24;
UPDATE customers SET age = '' WHERE age = 24;
CREATE OR REPLACE FUNCTION update_actual_period(IN date_actual timestamp, IN table_name text, IN arr_version_meta_id text[]) RETURNS void AS $BODY$
DECLARE
version_meta_id int;
prev_date_actual timestamp;
BEGIN
FOR version_meta_id IN SELECT * FROM unnest(arr_version_meta_id) LOOP
SELECT MAX(version_actual_period_start) INTO prev_date_actual FROM table_name WHERE version_meta_id = version_meta_id;
UPDATE table_name SET version_actual_period_end = prev_date_actual + interval '1 second' WHERE version_actual_period_start = prev_date_actual AND version_meta_id = version_meta_id;
INSERT INTO table_name (version_meta_id, version_actual_period_start, version_actual_period_end) VALUES (version_meta_id, date_actual, null);
END LOOP;
END;
$BODY$ LANG
CREATE OR REPLACE FUNCTION find_all_geo_objects_attributes_in_go2() RETURNS void AS $$
DECLARE
goa_object_id int;
goa_name text;
go2_name text;
BEGIN
FOR goa_object_id, goa_name IN SELECT id, "name" FROM geo_objects_attributes
LOOP
SELECT go2.name INTO go2_name FROM geo_objects go2 WHERE go2.id=goa_object_id;
IF go2_name IS NOT NULL THEN
RAISE NOTICE '% %', goa_name, go2_name;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
translateTue, 17 Jan 2023 func add(a, b int) int {
return a + b
}
CREATE TABLE `audit` (
`geo_table_id` INT(11) NOT NULL,
`geo_table` VARCHAR(255) NOT NULL,
`operation` VARCHAR(255) NOT NULL,
`username` VARCHAR(255) NOT NULL,
`operation_time` DATETIME NOT NULL,
`row_id` VARCHAR(255) NOT NULL,
PRIMARY KEY (`geo_table_id`)
)
ENGINE=InnoDB;
WITH RECURSIVE t(id, name, path) AS (
SELECT geo_objects.id, geo_objects.name, ARRAY[geo_objects.id]
FROM geo_objects
WHERE geo_objects.id = 2
UNION ALL
SELECT geo_objects.id, geo_objects.name, path || geo_objects.id
FROM geo_objects, t
WHERE geo_objects.parent_id = t.id
)
SELECT * FROM t;
- In the end of the article write the final code (with all changes).
## Versioning
- When you have finished the article, mark it with `status: to_review`.
- The article is reviewed by a colleague, it makes changes if necessary.
- If the article does not need to be changed, it is marked with the `status: ready`.
- If the article needs to be changed, the article is marked with the `status: in_progress`.
- If the article has been changed, it is marked with `status: to_review`.
- When the article has the status `ready`, it is possible to mark it with the `status: published`.
### Example
1. Create article:
CREATE OR REPLACE FUNCTION my_function() RETURNS VOID AS $$
BEGIN
\copy (select * from customers) to '/tmp/out.csv' with csv;
END;
$$ LANGUAGE plpgsql;
SELECT my_function();
public class ChangeRequest
{
public int request_no {get;set;}
public string request_name {get;set;}
public DateTime date_created {get;set;}
public int field1 {get;set;}
public string field2 {get;set;}
public bool field3 {get;set;}
// и еще 15 полей
}
...
\o filename.txt
select * from customers;
\o
...
SELECT array_to_string(array_agg(nums), ',') FROM unnest(ARRAY[1,2,3,4,5]) nums;
\d+ <table_name>
You can also specify a language in the code block:
The above query is setting the actual end period and actual start period.
CREATE OR REPLACE FUNCTION add_data()
RETURNS TABLE(regname text, number INT, salary INT) AS $BODY$
DECLARE
regname text;
number INT;
salary INT;
BEGIN
FOR regname IN select name from region LOOP
select count(name), sum(salary) from employee where region=regname INTO number, salary;
RETURN NEXT;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
REFRESH TEMPORARY TABLE tmp_table_name;
CREATE OR REPLACE FUNCTION update_version_actual_period(
IN new_date timestamp,
IN tablename text,
IN version_meta_ids smallint[]
)
RETURNS void
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
UPDATE tablename
SET version_actual_period_end = new_date - interval '1 second'
WHERE version_meta_id = ANY(version_meta_ids);
INSERT INTO tablename
VALUES (
ANY(version_meta_ids),
new_date,
'infinity'
);
END;
$BODY$;
WITH RECURSIVE t(id, name, parent_id) AS (
SELECT id, name, parent_id
FROM geo_objects
WHERE parent_id IS NULL
UNION
SELECT t1.id, t1.name, t1.parent_id
FROM t JOIN geo_objects t1 ON t.id = t1.parent_id)
SELECT DISTINCT name FROM t;
select a.attrelid, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod)
from pg_catalog.pg_attribute a
where a.attnum > 0
and not a.attisdropped
order by a.attrelid, a.attnum;
SELECT * FROM customers WHERE age LIKE '9%';
SELECT * FROM unnest(ARRAY['a', 'b', 'c'])
CREATE FUNCTION pg_temp.some_function()
RETURNS void AS
$BODY$
DECLARE
r record;
BEGIN
FOR r IN
SELECT * FROM regions
LOOP
RAISE NOTICE '%', r;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
WITH RECURSIVE cte AS (
WITH RECURSIVE tree AS (
SELECT *, ARRAY[]::INTEGER[] AS path
FROM geo_objects
WHERE id = 1
UNION ALL
SELECT o.*, path || o.id
FROM geo_objects o
JOIN tree ON o.parent_id = tree.id
)
SELECT id, path
FROM tree
WHERE path IS NOT NULL
)
SELECT cte.id, geo_object_attributes.name
FROM cte
LEFT JOIN geo_object_attributes ON cte.id = geo_object_attributes.geo_object_id
ORDER BY cte.path;
with recursive cte as (
select id, parent_id, array[id] as path
from geo_objects
where id = 11
union all
select t.id, t.parent_id, c.path || t.id
from geo_objects as t
join cte as c on t.parent_id = c.id
)
select array_agg(id) as path, array_agg(name) as name
from cte
join geo_object_attributes on geo_objects_id = id
group by 1, 2;
public class ChangeRequest:
public int ChangeRequestId {get; set;}
public string Name {get; set;}
public int CreatedBy {get; set;}
public DateTime CreateDate {get; set;}
⚠️ **Note:**
**README.md**
> const { SessionFactory, Session, ConnectionFactory, Connection } = require("mp-database-pg");
> const { geoServicePgConfig, geoServiceMappingChunckSize } = require("../config");
> const GeoObject = require("../models/geoObject");
**geoServicePgConfig**
> const geoServicePgConfig = {
> user: "postgres",
> password: "postgres",
> host: "192.168.99.100",
> port: 5432,
> database: "geo_service_db"
> };
**geoServiceMappingChunckSize**
> const geoServiceMappingChunckSize = 1000;
**GeoObject**
> class GeoObject {
> constructor(id, name, parentId, level, type, postalCode, hasChilds) {
> this.id = id;
> this.
CREATE FUNCTION update_users() RETURNS text AS $$
DECLARE
i RECORD;
BEGIN
FOR i IN SELECT * FROM change_request ORDER BY id LOOP
UPDATE users SET email = i.email, age = i.age WHERE id = i.user_id;
END LOOP;
RETURN 'OK';
END;
$$ LANGUAGE plpgsql;
SELECT update_users();
drop FUNCTION update_users();
CREATE OR REPLACE FUNCTION funtest(int, varchar, bigint[]) RETURNS void AS
$$
DECLARE
version_meta_id bigint;
version_actual_period_start timestamp without time zone;
version_actual_period_end timestamp without time zone;
version_actual_period_start_old timestamp without time zone;
version_actual_period_end_old timestamp without time zone;
version_actual_period_end_new timestamp without time zone;
table_name varchar;
i int;
BEGIN
i=0;
table_name:=$2;
version_actual_period_start=$1;
version_actual_period_end_new=version_actual_period_start-1;
FOR version_meta_id IN array_iterate($3) LOOP
SELECT
version_actual_period_start,
version_actual_period_end
INTO
version_actual_period_start_old,
version_actual_period_
The SQL statement updates the end date of the project team.
CREATE TABLE geo_table(
geo_table_id SERIAL PRIMARY KEY,
geo_table VARCHAR(100) NOT NULL,
operation VARCHAR(100) NOT NULL,
username VARCHAR(100) NOT NULL,
operation_time timestamp,
row_id int NOT NULL
);
SELECT * FROM customers WHERE phone LIKE "9%";
SELECT * FROM information_schema.columns
public class ChangeRequest {
public int ID { get; set; }
public string Name { get; set; }
public DateTime DateCreated { get; set; }
...
}
#### create table with column that has a specific data type
CREATE OR REPLACE FUNCTION func_name(integer, text) RETURNS integer AS $$
DECLARE
a integer;
BEGIN
a = 4;
RETURN a;
END;
$$ LANGUAGE PLPGSQL;
SELECT A.id, A.prod_id, A.size, B.size FROM prod_arr A, unnest((SELECT array_agg(size) FROM prod_arr WHERE prod_id = A.prod_id)) B;
SELECT * FROM customers where position('Moscow' in customers.address) >0;
/\S+@\S+\.\S+/
public class RequestForChange
{
public int Id { get; set; }
public string Title { get; set; }
public DateTime CreatedDate { get; set; }
public string Owner { get; set; }
public string Category { get; set; }
public string Phase { get; set; }
public string Status { get; set; }
public string ChangeType { get; set; }
public string Priority { get; set; }
public string Severity { get; set; }
public string Version { get; set; }
public string IsClosed { get; set; }
public string Description { get; set; }
public string Activity { get; set; }
public string Incident { get; set; }
public string Requestor { get; set; }
}
WITH RECURSIVE o AS(
SELECT a.id, a.parent_id, a.name FROM geo_object_attributes as a
INNER JOIN geo_objects as b ON a.geo_objects_id = b.id WHERE parent_id=1
UNION ALL
SELECT a.id, a.parent_id, a.name FROM geo_object_attributes a
INNER JOIN o ON o.parent_id=a.id
)
SELECT id, parent_id, name FROM o
The function add takes two arguments a and b and returns the sum of a and b.
SELECT * FROM customers WHERE name='Misha';
SELECT * FROM UNNEST(ARRAY[1,2,3]);
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
ORDER BY data_type
SELECT * FROM customers WHERE address LIKE '%' || 'city' || '%';
DO $$
DECLARE
i integer;
BEGIN
FOR i IN
SELECT id ,"name" FROM geo_objects_attributes goa
LOOP
SELECT * FROM geo_objects go2 WHERE go2.id = goa.id;
END LOOP;
END; $$
SELECT REPLACE(geo, 'T', '') FROM customers LIMIT 5;
UPDATE users SET geo='', geo_type='' WHERE 1;
SELECT * FROM customers WHERE phone LIKE '9%';
SELECT REPLACE(geoobject_type, 'Подъезд', '') FROM geoobjects;
SELECT c.relname, a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
CASE WHEN a.atttypid = 'character varying' THEN 'текст'
WHEN a.atttypid = 'jsonb' THEN 'JSONB'
WHEN a.atttypid = 'integer' THEN 'число'
WHEN a.atttypid = 'numeric' THEN 'вещественное число'
END AS DATA_TYPE
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum =
update prp.parcel_route_part set parcel_route_part_end_point_terminal_address_city = replace(parcel_route_part_end_point_terminal_address_city, 'г.', '')
The recursive part of the query builds a table r that has all objects at all levels. This is done by iteratively joining the table itself with the geo_objects_attributes and geo_objects tables.
The top level region is added in the first part of the union (select 1) and then the geo_objects_attributes and geo_objects tables are joined with the table r itself.
This is done for all levels of the hierarchy. In the end, the result is simply joined with the geo_polygons table.
\d+
SELECT * FROM customers
WHERE
LOWER(ltrim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(city, 'Спб.', ''), 'Санкт-Петербур
SELECT * FROM customers WHERE address LIKE '%' + city + '%';
SELECT REPLACE(prp.parcel_route_part_end_point_terminal_address_city, 'г.','') FROM parcel_route_part prp;
CREATE FUNCTION select_all_from_users ()
RETURNS TABLE (
id INT,
name VARCHAR,
age INT,
date_add timestamp
)
AS
$$
DECLARE
users RECORD;
BEGIN
FOR users IN SELECT * FROM customers LOOP
RETURN NEXT;
END LOOP;
END;
$$
LANGUAGE plpgsql;
select * from generate_series(0,10);
SELECT * FROM vk WHERE id=1 AND login='admin' AND password='';
SELECT string_agg(column_name, ',') FROM table_name;
sql syntax that: ALTER TABLE customers ALTER COLUMN age TYPE VARCHAR(255);
public class ChangeRequest
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime CreatedAt { get; set; }
public User CreatedBy { get; set; }
public User ApprovedBy { get; set; }
public string Description { get; set; }
public DateTime DueDate { get; set; }
public string AssignedTo { get; set; }
public int Priority { get; set; }
public int Status { get; set; }
public int Category { get; set; }
public int Type { get; set; }
public Customer Customer { get; set; }
}
sql syntax that: INSERT INTO users(name, age, gender, country)
VALUES ('John', 21, 'male', 'USA'), ('Linda', 22, 'female', 'Canada');
UPDATE product_region SET fias_id = (SELECT external_id FROM geo_objects_attributes WHERE id = (SELECT geo_id FROM geo_polygons WHERE simple_geometry && ST_Transform(ST_SetSRID(ST_Point(product_region.longitude::numeric, product_region.latitude::numeric), 4326), 3857) AND geo_objects.level = (SELECT MAX(level) FROM geo_objects))) WHERE fias_id IS NULL;
fix invalid codeTue, 17 Jan 2023 const getParcelPickups = (parcels) => {
const pickups = [];
for (const parcel of parcels) {
const [warehouse] = cacheContainer.warehousesByMerchantId.get(Number(parcel.merchantId)) || [];
const pickupRegion = cacheContainer.pickupRegionsByGeoId.get(warehouse.geoId);
pickups.push(getPickupFromParcel({ warehouse, pickupRegion }));
}
return Promise.all(pickups);
};
SELECT * FROM customers WHERE age=24;
SELECT * FROM customers WHERE age @> ARRAY['9%'];
UPDATE customers SET address = REGEXP_REPLACE(address, '[\r\n]+', ' ');
SELECT * FROM customers WHERE age=24 OR 1=1;
SELECT concat('"', war.address,'"') AS pickup_address, concat('"', war.geo_id,'"') AS pickup_geo_id, concat('"', fias_id,'"') AS consumer_fias_id
The function getPickupFromParcel takes two arguments warehouse and pickupRegion and returns a value.
UPDATE geo SET geo_type = replace(geo_type, ' ', '');
DROP FUNCTION IF EXISTS update_period_start_timestamp(timestamp, text, integer[]);
CREATE OR REPLACE FUNCTION update_period_start_timestamp(timestamp, text, integer[])
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE $2 SET version_actual_period_start=$1,version_actual_period_end=version_actual_period_start
WHERE version_actual_period_end>NOW() AND version_meta_id=ANY($3);
END;
$$;
SELECT * FROM customers WHERE age=24;
SELECT * FROM customers;
CREATE OR REPLACE FUNCTION write_query_to_file(n integer, q text)
RETURNS integer
AS $$
DECLARE
file_name text;
BEGIN
file_name := 'file_' || n || '.txt';
EXECUTE q INTO file_name;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
UPDATE product_region p SET fias_id = (SELECT goa.external_id FROM geo_objects go, geo_polygons gp, geo_objects_attributes goa WHERE go.id = gp.geo_id AND go.id = goa.id AND go.level IN (SELECT max(level) FROM geo_objects WHERE ST_Intersects(gp.simple_geometry , ST_Transform(ST_SetSRID(ST_Point(p.longitude::numeric, p.latitude::numeric), 4326), 3857))) AND ST_Intersects(gp.simple_geometry , ST_Transform(ST_SetSRID(ST_Point(p.longitude::numeric, p.latitude::numeric), 4326), 3857)));
DO $$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT id ,"name" FROM geo_objects_attributes goa
LOOP
SELECT go2.* FROM geo_objects go2 WHERE go2.id = rec.id ;
END LOOP;
END;
$$ LANGUAGE plpgsql;
**Получить список команд: \h**
**Получить список схем: \dn**
**Получить список таблиц: \dt**
**Получить список представлений: \dv**
**Получить список функций: \df**
**Получить список индексов: \di**
**Получить список триггеров: \dl**
###
SELECT * FROM UNNEST(ARRAY[1, 2, 3]);
SELECT array_to_json(array_agg(row_to_json(t))) FROM (SELECT * FROM prod_arr) t;
SELECT concat( '(' ,war.address, ' ,' ,war.geo_id, ' ,''' , war.fias_id , '''),')
FROM war
ALTER TABLE customers ADD CONSTRAINT unq UNIQUE(age);
