1

How can a query or procedure return an empty copy of a new record?

Ideally suited for objects which are in a transactional allocation process: It is highly preferable to have authoritative defaults and NULLS for arbitrary tables when constructing new objects() that may-or-may not ultimately be saved to the database.

For example, a CMS user relies on AJAX which calls for a new post() or new product() dynamically. Rather than trusting stored profiles to set values in the scripting language, a blank "instance" of a row would be rather useful.

2 Answers 2

0

From a pure database perspective this isn't really possible, unless you made a View that had a single row with the default values you needed that matched the schema of your table.

This question might be better suited on StackOverflow for how to construct a new instance of an entity object in JavaScript or whichever other object-oriented languages you're using.

13
  • While I can understand the desire to offload this to the OOP scripting language; it rather flies in the face of the goal here and answers a different question. If it is possible with a View; would it be workable with a temp table somehow? Complexity or runtime is not important as we can save the result to existing stored profiles - which data class construction uses on the OOP side - but we need the results of a NULL insert without actually changing the primary key or adding to the dataset Commented Jan 5, 2021 at 20:05
  • @GaretClaborn The reason for the suggestion that your question is better off in an OOP forum like StackOverflow is because it's not really a reasonably conceivable question for databases. For comparison it's as reasonable of a request as if I asked how to use only HTML as a database to store all my data. I don't mean this offensively or to be pedantic, but it's literally the wrong tool for the job, and you'll get much better solutions for your end goal asking the question from that perspective. To try to answer your question anyway, yes you can use a TempTable (or global TempTable depending...
    – J.D.
    Commented Jan 5, 2021 at 20:21
  • 1
    ...on the scope of access needed from your application side (I would guess a global TempTable, more likely). But you run the risk of the Temp database getting cleared out (e.g. on server restart). A physical table that's a copy of your actual table might help you there. There's a number of options that can be shoehorned to accomplish what you're trying to do, I think a View is your cleanest bet though. Usually the ORM, micro-ORM, or manually mapped concrete class can be used to define the defaults of an entity object on the application side though, as is normal practice.
    – J.D.
    Commented Jan 5, 2021 at 20:28
  • yea, as mentioned the application has been using such a technique for a long time (across many projects.) indeed using an ORM. what im requesting is actually intended for use in the ORM's cache. because so many mappings are involved which can change dynamically there have been some rather nuanced issues. while i have nothing against a View based approach, we really only need it to exist for the duration of a stored procedure which is NOT called every object instantiation but only on the UpdateSchema() command issued from the ORM. I really do get where you are coming from and expected as much.. Commented Jan 6, 2021 at 20:32
  • 1
    @GaretClaborn Just to make sure I can properly do my homework, is this what you're referring to?
    – J.D.
    Commented Jan 6, 2021 at 20:58
0

Approach it the opposite way -- Your app determines the object definitions. When the code ultimately decides to save an instance to the database, then it constructs an appropriate INSERT, or multiple inserts, to store the data in one or more table(s).

2
  • wellll, appreciate it but alas no. what you describe is already the approach being taken by the app for several years. the question is seeking to approach this purely from the database side due to needs which cannot be met outside the database with the same level of veracity. unfortunately that answers a different question than being asked. I've got close from SELECT t.* FROM (SELECT NULL) x LEFT JOIN "myTable" t ON false; but no default values unfortunately. While I can understand you may not like the approach, please consider how to achieve it anyway if remotely possible. thanks Commented Jan 5, 2021 at 20:15
  • Side note, as mentioned in another answer -- complexity or runtime is not important as we can save the result to existing stored profiles -- which we could use to then continue doing instantiation through the app as normal and as you are suggesting. As long as the result is something we can poll from time to time in order to update code generators that would be fine. However just looping over all column names and providing in-app defaults is causing some issues with the number of datasource we are normalizing to a standard set of schemas Commented Jan 5, 2021 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.