Search Postgresql Archives

Re: Dynamic PL/pgSQL select query: value association propblem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks for answering.

Zitat von Pavel Stehule <pavel.stehule@xxxxxxxxx>:

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.





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux