Обсуждение: Dynamic PL/pgSQL select query: value association propblem

Hi all

I would like to have a generic trigger function that compares on
insert if there is already a record in the table with the very same
values. Using PL/pgSQL ( I am not bound to that) I know the insert
record structure from the new record and I can build a select query
dynamically from the catalogue, but I do not know to associate the new
record values to the corresponding columns. An example
Table T has columns Q and L, in that order. If I create an insert
trigger function I have the new values in new.L and new.Q. From the
catalogue I can create a the select query S_QUERY:
select count(*) > 0 from T where A = $1 and B = $2. But when I want to
EXECUTE S_QUERY USING it fails because I cannot use something like
NEW[1].

Is there a way to convert the record type into an array type? Or is
there even a way to do it more directly like WHERE T.RECORD = NEW?

Kind regards Thiemo

--
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Thanks for answering.

Zitat von Pavel Stehule <pavel.stehule@gmail.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.

    Thiemo Kellner, NHC Barhufpflege wrote:

> > 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.

It could be written without refering to any individual column:

 IF EXISTS (select 1 from tablename
     where tablename.* is not distinct from NEW)
 THEN
   -- do something
 END IF;

But since the select cannot see not-yet-committed changes from other
sessions, such a trigger cannot reliably detect duplicates, unless
you make sure that there are no concurrent writes to the table.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Zitat von Daniel Verite <daniel@manitou-mail.org>:

>     Thiemo Kellner, NHC Barhufpflege wrote:
>
>> > 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.
>
> It could be written without refering to any individual column:
>
>  IF EXISTS (select 1 from tablename
>      where tablename.* is not distinct from NEW)
>  THEN
>    -- do something
>  END IF;

Wow, I shall see if I can apply this pattern to more.

> But since the select cannot see not-yet-committed changes from other
> sessions, such a trigger cannot reliably detect duplicates, unless
> you make sure that there are no concurrent writes to the table.

This is not trigger specific but always a problem of concurrency. If
you let two processes do SCD2 on the same table at the same time on
the same business keys, you will get a messed-up table.

--
+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.

Hi all

I would like to have a generic trigger function that compares on insert if there is already a record in the table with
thevery same values. Using PL/pgSQL ( I am not bound to that) I know the insert record structure from the new record
andI can build a select query dynamically from the catalogue, but I do not know to associate the new record values to
thecorresponding columns. An example Table T has columns Q and L, in that order. If I create an insert trigger function
Ihave the new values in new.L and new.Q. From the catalogue I can create a the select query S_QUERY:
 
select count(*) > 0 from T where A = $1 and B = $2. But when I want to EXECUTE S_QUERY USING it fails because I cannot
usesomething like NEW[1].
 

Is there a way to convert the record type into an array type? Or is there even a way to do it more directly like WHERE
T.RECORD= NEW?
 

Kind regards Thiemo

--
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

----------------------------------------------------------------

Why not let the database do this job for you?
Just create a proper UNIQUE constraint on the columns you are interested in, and catch a "duplicate key" exception in
yourapp.
 

Regards,
Igor Neyman

On 02/16/18 14:47, Thiemo Kellner, NHC Barhufpflege wrote:
> Thanks for answering.
> 
> Zitat von Pavel Stehule <pavel.stehule@gmail.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.

It's terrific! This works! :-) :-) :-)

-- 
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
SIP/iptel.org: thiemo.kellner
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC