Default Values vs. server side functions + Sequence fills

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
durumdara
Posts: 6
Joined: Fri 29 Apr 2011 11:16

Default Values vs. server side functions + Sequence fills

Post by durumdara » Tue 12 Apr 2016 12:56

Dear Support!

I have found an interesting thing.

From the document I read that we must provide sequence name and keyfields for get IDs in interesting records (f. e. masters, or which IDs used in next statements).
It's ok.

But:
I found a "strange" property named "DefaultValues" (?) which fills up the fields with PG table field defaults.

The first question was: what happens with server side evaluated expressions?
Ok, simple expressions can be evaluated by client side as "1", "true", etc.
But what happens with complexes like "current_timestamp" and so on?

The story continued with a developer's experiments.

He said that serials (default nextval()) also evaluated, so he get the next identifier without KeyFieldNames/SequenceName pairs.

It can be true? What happens there? Do you silently call the default expressions on PGQuery.Append, and put the result into the fields?
Can we use this property, or it have limitations?

He said he found a "problem" (?) that when he got this value (f. e. 53), and KeyFieldNames/SeqName filled, it overwrote this value with new.
It can be true? I have no example to it now, but what is your theory about it?

Thanks for your great work!

dd

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Default Values vs. server side functions + Sequence fills

Post by azyk » Thu 14 Apr 2016 09:04

If the property of the TPgQuery.Options.DefaultValues dataset is set to True, during the call of the TPgQuery.Append method, PgDac will query server for values for the DEFAULT fields and will fill the DEFAULT fields of the dataset on the client side with the received values.

The aforesaid behavior refers to the fields with serial types. For more details on how PostgreSQL interprets serial types, see the PostgreSQL documentation:
http://www.postgresql.org/docs/current/ ... YPE-SERIAL

If values for TPgQuery.KeySequence and TPgQuery.KeyFields are set in dataset, during the generation of the INSERT query, PgDAC will take values of the key field not from dataset, but will call the NEXTVAL function, in which the value from TPgQuery.KeySequence will be an argument. For example:

Code: Select all

INSERT INTO table1
  (id, ...)
VALUES
  (NEXTVAL('table1_id_seq'), ...)
RETURNING
  id
When the TPgQuery.Append method is called, during the call for TPgQuery.Post, PgDAC will generate the INSERT query, as described above, and will pass the generated query to the server.

Post Reply