1

Topic: Prompt on "artful" request

Greetings! I try to make such request, which: 1) fulfills check on existence, 2) if does not exist - interposes, 3) if exists -  on first two: in the first case should return 0, differently - 1 it is especially interesting, as it to make one request to a DB provided that that is checked/is inserted - can be more than one field. For example, we have such lines: string a string b string c the request should (in the pseudocode): func = (string h, int id) {if h exists {return (true, select id);} else {insert (h, id); return (false, id);}} struct {pair (bool, int) res_a; pair (bool, int) res_b; pair (bool, int) res_c;} res = {func (' rohgreiuehgreigheih ', 33), func (' srdkrughrewiugheiue ', 33), func (' yuiruywrtbrqynbgngb ', 33)}; tried to explain as could...

2

Re: Prompt on "artful" request

2/21/2018 16:14, niXman writes:> it is especially interesting, as it to make one request to a DB in any way. It is possible to envelop all it in the unit and to fulfill "at one stroke", but it in any way "one request". Posted via RSDN NNTP Server 2.1 beta

3

Re: Prompt on "artful" request

Hello, niXman, you wrote: X> I try to make such request, which: 1) fulfills check on existence, 2) if does not exist - interposes, 3) if exists -  X> on first two: in the first case should return 0, differently - 1 you I exist mandatory to return? If there is no that it is possible through RETURNING X> is especially interesting as it to make one request to a DB provided that that is checked/is inserted - can be more than one field. Basis? In  it is possible to try.

4

Re: Prompt on "artful" request

Hello, Alex. Che, you wrote: AC> it is possible to envelop all it in the unit and to fulfill "at one stroke", where to read?

5

Re: Prompt on "artful" request

Hello, night beast, you wrote: NB> to you I exist mandatory to return? If there is no that it is possible through RETURNING yes, mandatory. NB> basis? In  it is possible to try. .

6

Re: Prompt on "artful" request

Hello, niXman, you wrote: NB>> to you I exist mandatory to return? If there is no that it is possible through RETURNING X> yes, mandatory. NB>> basis? In  it is possible to try. X> . I write without check. Probably somewhere a jamb with v (select 1 id, ' 1 ' value union all select 2, ' 2 '), a (select id, value from tbl where id in (select id from v)), b (insert into tbl (id, value) select v.* from v left join an on a.id = v.id where a.id is null returning *) select false, a.* from an union all select true, b.* from b

7

Re: Prompt on "artful" request

Hello, niXman, you wrote: X> greetings! X> I try to make such request, which: 1) fulfills check on existence, 2) if does not exist - interposes, 3) if exists -  At first SELECT... FOR UPDATE And then normal PL/SQL the code with IF exists (...

8

Re: Prompt on "artful" request

Hello, niXman, you wrote: X> it is especially interesting, as it to make one request to a DB provided that that is checked/is inserted - can be more than one field. Here simplified code PostgreSQL, checked up, works. Returns true/false and id if record already is that (true, old id) or (false, new id) if records were not also it is added. If there was a restriction on uniqueness of a line it would be possible to make is even easier. Checked on a following code: CREATE TABLE tbl (id INTEGER, str VARCHAR (100)); INSERT INTO tbl (id, str) VALUES (1, ' qwe '); WITH res AS (INSERT INTO tbl (str, id) (SELECT $new_str, $new_id WHERE NOT EXISTS (SELECT 1 FROM tbl WHERE str = $ new_str)) RETURNING id) SELECT NOT EXISTS (SELECT id FROM res), COALESCE ((SELECT id FROM tbl WHERE str = $ new_str), $new_id); SELECT * FROM tbl; I conditionally designated Entering variables $ character, at check stupidly substituted values

9

Re: Prompt on "artful" request

Hello, niXman, you wrote: NB>> basis? In  it is possible to try. X> . https://stackoverflow.com/a/42217872