From: | "Thiemo Kellner, NHC Barhufpflege" <thiemo(dot)kellner(at)gelassene-pferde(dot)biz> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Dynamic PL/pgSQL select query: value association propblem |
Date: | 2018-02-16 13:47:22 |
Message-ID: | 20180216144722.19431u5fi817qmsg@www.gelassene-pferde.biz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks for answering.
Zitat von Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Why you don't create query like
>
> EXECUTE 'SELECT xxx FROM TAB WHERE A = $1.x AND B = $1.y' USING NEW;
I shall try. This would be the direct way, but I doubt the placeholder
$1 can be a record.
> I don't understand tou your case, but usually count(*) > 0 looks like
> antipattern - probably you want to use EXISTS(...)
It is about SCD2
(https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row)
and new versions of business key pay load. I want to avoid bloating
the table by useless because still valid/active business key pay load
versions, so I want to check beforehand whether I need to
invalidate/deactivate the valid/active record for the given business
key. Example
Table T with columns A (number), B (text), K (text, business key),
VF(date), VU (date)
content record 1: A=1, B='old', K='key1', vf=yesterday, vu={null}
If I insert (1, 'old', 'key1') I do *not* need to invalidate record 1
as there is no gain of information (identical values for A and B). If
I still do, I bloat the table with a useless business key pay load
version. (What an awkward expression I invented there but record
version is not cleat enough in my opinion.)
If I insert (1, 'new', 'key1') I must invalidate record 1 by updating
VU to today. Otherwise I have overlapping validity.
If I insert (1, 'new', 'key2') I do *not* need to invalidate a record
because key2 is not yet in the table. If I still update, it is just
heating the atmosphere with CPU power for nothing.
--
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-02-16 13:54:14 | Re: postgres started without auto vaccum |
Previous Message | mariusz | 2018-02-16 13:20:03 | Re: strange construct with RETURN within plpgsql |