firebird
firebird copied to clipboard
FirebirdSQL
RECORD TYPES for PSQL [CORE740]
Submitted by: Valdir Stiebe Junior (ogecrom)
Relate to CORE2621 Replaces CORE2686
Votes: 5
SFID: 1421984# Submitted By: ogecrom
I don't know the complexity of this but it will be good if I could create my own type in PSQL. For example:
create record type TPERSON ( NAME varchar(100), AGE integer);
I don't think that this is useful for domains or table columns. But it is useful for reducing the amount of code and parameters between stored procedures.
For example, I use a "instead of insert" trigger for a view. In this trigger I could fill a variable of this type and then pass it to a stored procedure wich expects a parameter of this type. The assignment and reading of the values would be like http://MYVAR.NAME. This procedure, tests some values of the type, but it must pass the entire type to other procedure that use all the other values.
If you think that this feature should be added in domains (and consequently in table columns), instead of a record type, remember to allow the use of "select column.*", "select http://column.NAME" and "select column" (as an alias for "select column.*").
If you need a better explaining of my example, or another question, just ask.
Commented by: Alice F. Bird (firebirds)
Date: 2006-02-02 13:38 Sender: ogecrom Logged In: YES user_id=443816
I think that user defined data types as plugins isn't the best option. Because the common user already avoid creating UDFs. It would be much nicer to create my UDFs inside the database using PSQL just as I'm already do with triggers and procedures. So I could not worry about linux or windows in deployment and about upgrading for new firebird versions.
Commented by: Alice F. Bird (firebirds)
Date: 2006-02-02 11:09 Sender: danielrail Logged In: YES user_id=238419
I would vote against it.
But, I would probably vote for the support of user defined data types(as per the SQL Standard), which would give the user/developer the flexibility to define any data types. And, it would be up to the user/developer to implement the proper support in their development environment. With the SQL Standard user defined data types, it's up to the user/developer to define how the data is structured, accessed and compared.
And, I would see the user defined data types as plugins (DLLs as UDFs are). As an example of usefulness, someone could develop an user defined data type for spatial data than trying to have that data type incorporated into the engine itself.
But, I don't know what that would mean in relations to the BLR.
Commented by: Alice F. Bird (firebirds)
Date: 2006-02-02 10:05 Sender: dimitr Logged In: YES user_id=61270
The row datatypes are declared in the SQL spec. And such a feature is quite useful in real life :-)
Commented by: Alice F. Bird (firebirds)
Date: 2006-02-02 01:10 Sender: seanleyne Logged In: YES user_id=71163
I am not aware of the SQL standard supporting such a definition, is there such a definition?
If so, please provide appropriate details.
If not, please provide a further details which would justify implementing a non-standard feature, beyond a 'nice to have' functionality.
Modified by: @dyemanov
Component: Engine [ 10000 ]
assignee: Dmitry Yemanov [ dimitr ]
SF_ID: 1421984 =>
Commented by: Valdir Stiebe Junior (ogecrom)
Another use for record types would be at the "into" statements. And the for select/execute into could even create an implicit record using the field names at the select statement.
for select FIELD1, FIELD2 from TABLE into record :RECORD_NAME do if (RECORD_NAME.FIELD1 ... )
Modified by: @pcisar
Workflow: jira [ 10764 ] => Firebird [ 15168 ]
Modified by: Sean Leyne (seanleyne)
description: SFID: 1421984# Submitted By: ogecrom
I don't know the complexity of this but it will be good if I could create my own type in PSQL. For example:
create record type TPERSON ( NAME varchar(100), AGE integer);
I don't think that this is useful for domains or table columns. But it is useful for reducing the amount of code and parameters between stored procedures.
For example, I use a "instead of insert" trigger for a view. In this trigger I could fill a variable of this type and then pass it to a stored procedure wich expects a parameter of this type. The assignment and reading of the values would be like http://MYVAR.NAME. This procedure, tests some values of the type, but it must pass the entire type to other procedure that use all the other values.
If you think that this feature should be added in domains (and consequently in table columns), instead of a record type, remember to allow the use of "select column.*", "select http://column.NAME" and "select column" (as an alias for "select column.*").
If you need a better explaining of my example, or another question, just ask.
=>
SFID: 1421984# Submitted By: ogecrom
I don't know the complexity of this but it will be good if I could create my own type in PSQL. For example:
create record type TPERSON ( NAME varchar(100), AGE integer);
I don't think that this is useful for domains or table columns. But it is useful for reducing the amount of code and parameters between stored procedures.
For example, I use a "instead of insert" trigger for a view. In this trigger I could fill a variable of this type and then pass it to a stored procedure wich expects a parameter of this type. The assignment and reading of the values would be like http://MYVAR.NAME. This procedure, tests some values of the type, but it must pass the entire type to other procedure that use all the other values.
If you think that this feature should be added in domains (and consequently in table columns), instead of a record type, remember to allow the use of "select column.*", "select http://column.NAME" and "select column" (as an alias for "select column.*").
If you need a better explaining of my example, or another question, just ask.
Commented by: Sean Leyne (seanleyne)
Edited the details/entries converted from the SF tracker for readability.
This looks like a struc in C or a record type in Pascal. I believe that a lot of development is needed to achieve this. If JSON will be supported in version 6 there is no need for further development on record types because JSON is a record type or nested record types by default (variable record types in pascal). The JSON development covers the need for record types.
I don't think that this is useful for domains or table columns. But it is useful for reducing the amount of code and parameters between stored procedures.
Good feature, but then it should report dependencies error if you alter such a type which is parameter of the procedure (without changing a procedure body it will be bad thing without such error)
