Loss of some parts of the function definition

Hi

2015-04-30 13:44 GMT+02:00 Sergey Grinko <sergey.grinko@gmail.com>:

Hi,

Dear developers, I have a request to you.

Now create a script in the application of its function parameters and
return values can be declared using %TYPE.
However, when you save the script is stored inside the server only what is
considered his body. Thus, we obtain:
1) loss of the custom formatting.
2) loss of communication parameters and return types with these types of
fields to create the function.
3) multidimensional arrays are transformed into one-dimensional: [][] -> []
4) loss of data accuracy: numeric(n,m) -> numeric

Please - how to save and restore the entire text of the definition to
CREATE END; unchanged.

I am afraid, it is not possible

Postgres doesn't distinguish between multidimensional and one dimensional
arrays - multidimensional is just syntax suger, same is function arguments
- Postgres doesn't store precision for parameters. type%TYPE is translated
to target type outside plpgsql function. These informations are not saved,
so you cannot to take it from PostgreSQL

Regards

Pavel Stehule

I agree that it is better to show what really works.
I propose to allow additional option through a source code which is made on
the basis of a compilation of metadata.
This will solve the problem.

2015-04-30 16:19 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:

2015-04-30 15:08 GMT+02:00 Sergey Grinko <sergey.grinko@gmail.com>:

That's what I have to do now.
But there is some problem.
When you try to build the update script I get to Git code is always
different from what I see in the database.
It is not right.
MSSQL Server, Oracle, ... always saving of the full text DDL.
I do not understand why PostgreSQL believe that part of the source
function must be removed !?

I can understand to problem, but it doesn't help to you. Postgres displays
the code, that is really used. So we can speak what is more wrong -
displaying original but not used code, or displaying really used code.

I am thinking so current solution is better - any other solution mean 2x
stored data, that can be partially inconsistent.

It cannot be comparable with Oracle - because it is different technology.

2015-04-30 15:59 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:

2015-04-30 14:52 GMT+02:00 Sergey Grinko <sergey.grinko@gmail.com>:

Yes, I understand that.
So I ask to implement saving of the full text DDL.
This will allow developers to be able to save a meaning at the level of
the source code.
I ask to make sure that the function pg_get_function_def () returns
previously stored full text DDL, instead of generating input and output
parameters based on metadata.

I don't see a sense of this - usually much better is storing code to
files and using GIT and other.

Surely, you can safe code to any custom table.

Regards

Pavel

2015-04-30 15:46 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

2015-04-30 13:44 GMT+02:00 Sergey Grinko <sergey.grinko@gmail.com>:

Hi,

Dear developers, I have a request to you.

Now create a script in the application of its function parameters and
return values can be declared using %TYPE.
However, when you save the script is stored inside the server only
what is considered his body. Thus, we obtain:
1) loss of the custom formatting.
2) loss of communication parameters and return types with these types
of fields to create the function.
3) multidimensional arrays are transformed into one-dimensional: [][]
-> []
4) loss of data accuracy: numeric(n,m) -> numeric

Please - how to save and restore the entire text of the definition to
CREATE END; unchanged.

I am afraid, it is not possible

Postgres doesn't distinguish between multidimensional and one
dimensional arrays - multidimensional is just syntax suger, same is
function arguments - Postgres doesn't store precision for parameters.
type%TYPE is translated to target type outside plpgsql function. These
informations are not saved, so you cannot to take it from PostgreSQL

Regards

Pavel Stehule

--
Yours faithfully, Sergey Grinko
Email: sergey.grinko@gmail.com

--
Yours faithfully, Sergey Grinko
Email: sergey.grinko@gmail.com

--
Yours faithfully, Sergey Grinko
Email: sergey.grinko@gmail.com

--
Yours faithfully, Sergey Grinko
Email: sergey.grinko@gmail.com

2015-04-30 15:34 GMT+02:00 Sergey Grinko <sergey.grinko@gmail.com>:

I agree that it is better to show what really works.
I propose to allow additional option through a source code which is made
on the basis of a compilation of metadata.
This will solve the problem.

You can to teach PostgreSQL function to use precision and derived types -
it is not plpgsql issue only - it is related to all PL.

There was some proposals in this area. Currently it is much better
situation than year ago, because plpgsql use binary cast instead IO cast
now.

Regards

Pavel Stehule

On 4/30/15 6:44 AM, Sergey Grinko wrote:

Now create a script in the application of its function parameters and
return values can be declared using %TYPE.
However, when you save the script is stored inside the server only what
is considered his body. Thus, we obtain:

...

We actually mung things a lot worse when it comes to views, so I'm
curious why you're only worried about the problems with stored functions?

FWIW, I think the best 'solution' to this right now is to actually keep
your original definitions as files in your VCS and use something like
sqitch for deployment. Taken to it's logical extreme, that means that
the only thing you ever 'patch' is an actual table (via ALTER TABLE), or
indexes. Everything else essentially gets treated like regular code.

That's still not terribly satisfying since unlike other forms of
software you now have all that definition both in your VCS and the
database itself, but ISTM that's a much bigger problem than the small
amount of info we lose from stored functions...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thank you Jim!
Views, they also have the problem. In my practice I use them very little,
so do not just remember them.
Somewhere I read that already are going to introduce their storage source.
If I find this source, then I write the link here.
I am a supporter of conservation of the source code.
I hope that the PostgreSQL developers still implement the storage of the
full DDL and PostgreSQL then receive another plus in competition with
commercial databases.
01 Май 2015 г. 23:03 пользователь "Jim Nasby" <Jim.Nasby@bluetreble.com>
написал:

About view.
I found where I saw it was a discussion solve some problems with view
https://wiki.postgresql.org/wiki/Todo#Views_and_Rules, ie it is in the list
of TODO, so there is a chance that it will be implemented.
03 Май 2015 г. 12:15 пользователь "Sergey Grinko" <sergey.grinko@gmail.com>
написал: